Inline Expressions

Dataset facts and dataset dimensions can both include an expression field containing a row-level SQL expression. For facts, the expression transforms source data before it's aggregated: useful for unit conversions, conditional logic, and rounding. For dimensions, the expression transforms grouping values: useful for date truncation, bucketing, and string manipulation.

In both cases, inline expressions let you define calculations directly on a field, next to the data they read. An expression that aggregates rows (SUM(...), COUNT(...)) is a metric instead.

The optional format field (facts and metrics) controls display formatting, either as a simple string shorthand (currency, percent, number) or a full object with properties like decimals, currency, and locale. When a user drags the field onto a card, the format seeds the card's default display settings.

When to Use Fact Expressions vs Metrics

Expression Syntax

Two authoring styles are supported.

Plain field references:

expression: "amount_cents / 100"

Brace notation (useful when field names contain special characters, or for clarity):

expression: "{amount_cents} / 100"

Both produce the same result. When you save, field names are resolved against the dataset's fields. An expression can also use the column named in its own sourceField.

Referencing Other Facts

A fact expression can reference other fields in the same dataset, including other facts. If you have a fact amount_usd with expression amount_cents / 100, another fact can build on it:

facts:
  - name: amount_usd
    expression: "amount_cents / 100"
    sourceField: amount_cents
    dataType: number
    defaultAggregate: SUM
  - name: amount_with_tax
    expression: "amount_usd * 1.08"
    dataType: number
    defaultAggregate: SUM

Supported SQL Constructs

• Arithmetic: +, -, *, /
• CASE WHEN: CASE WHEN status = 'active' THEN amount ELSE 0 END
• CAST: CAST(amount_cents AS DECIMAL) / 100
• Functions: ROUND(...), COALESCE(...), CONCAT(...), ABS(...), NULLIF(...)
• EXTRACT: EXTRACT(YEAR FROM order_date)

Aggregate functions (SUM(...), COUNT(...), AVG(...)) make an expression an aggregate. Put those in a dataset metric; in a fact or dimension they are refused with aggregate_in_row_field.

Examples

Currency Conversion

facts:
  - name: amount_usd
    label: "Amount (USD)"
    dataType: number
    sourceField: amount_cents
    expression: "amount_cents / 100"
    defaultAggregate: SUM
    format: currency

Per-Row Rate

facts:
  - name: conversion_rate
    label: "Conversion Rate"
    dataType: number
    sourceField: conversions
    expression: "conversions / NULLIF(visits, 0) * 100"
    defaultAggregate: AVG
    format: percent

Conditional Logic

facts:
  - name: active_revenue
    label: "Active Revenue"
    dataType: number
    sourceField: amount
    expression: "CASE WHEN status = 'active' THEN amount ELSE 0 END"
    defaultAggregate: SUM
    format: currency

Rounding

facts:
  - name: amount_rounded
    label: "Amount (Rounded)"
    dataType: number
    sourceField: amount
    expression: "ROUND(amount, 2)"
    defaultAggregate: SUM
    format: number

Aggregate Expression (a Metric)

When the expression aggregates rows itself, it's a dataset metric, not a fact. It goes in the top-level metrics list and names its dataset:

metrics:
  - name: avg_order_value
    dataset: orders
    label: "Average Order Value"
    dataType: number
    expression: "SUM(amount) / NULLIF(COUNT(DISTINCT order_id), 0)"
    format: currency

Format Specification

The format field controls how a fact's or metric's values are displayed. It supports two forms: a string shorthand for common cases, and a full object for fine-grained control.

String Shorthand

For quick setup, pass a format type as a string:

facts:
  - name: amount_usd
    expression: "amount_cents / 100"
    sourceField: amount_cents
    dataType: number
    format: currency

Supported string values:

Object Form

For more control, pass a format object with specific properties:

facts:
  - name: revenue_eur
    expression: "amount_cents / 100"
    sourceField: amount_cents
    dataType: number
    format:
      type: currency
      currency: EUR
      decimals: 0
      locale: de-DE
      thousandsSeparator: true

metrics:
  - name: margin_pct
    dataset: orders
    expression: "(SUM(amount) - SUM(cost)) / NULLIF(SUM(amount), 0)"
    dataType: number
    format:
      type: percent
      decimals: 1
      percentValueMode: fraction

Format Object Reference

Format Precedence

When a user drags a fact or metric with a format onto a card, the format seeds the card's default display settings. The precedence order is:

1. User-set formatting (highest priority): any formatting the user explicitly configures on the card
2. Semantic format: the format value from the field or metric definition
3. Visual defaults: Semaphor's built-in defaults

Aggregation Behavior

Fact Expressions

A fact expression is row-level. When a card uses the fact, Semaphor wraps the expression in the fact's defaultAggregate, or in the aggregate the card chooses.

# This fact:
expression: "amount_cents / 100"
defaultAggregate: SUM

# Becomes this in the generated SQL:
# SUM(amount_cents / 100)

Use AVG, MIN, MAX, MEDIAN, or another supported aggregate when that matches the fact's meaning:

expression: "quality_score"
defaultAggregate: AVG

Aggregate Expressions

An expression that already contains aggregate functions is used as written, and it belongs in a dataset metric:

# This dataset metric's expression:
expression: "SUM(amount) / NULLIF(COUNT(DISTINCT order_id), 0)"

# Is used directly in the generated SQL:
# SUM(amount) / NULLIF(COUNT(DISTINCT order_id), 0)

Non-Numeric Expressions

Facts should be numeric. If you need to count non-numeric values, model a fact with defaultAggregate: COUNT, or a dataset metric such as COUNT(DISTINCT customer_id).

Validation Errors

When you save a domain template with expressions or fact filters, Semaphor validates them. These codes apply to fact expressions and dataset metrics:

Restrictions

• AS aliasing: amount_cents AS usd is not supported. Use the field's name instead.
• No aggregates in facts or dimensions: Facts and dimensions are row-level. Aggregate functions (SUM, COUNT, etc.) belong in metrics.
• Cross-dataset references: Inline expressions can only reference fields within the same dataset. For cross-dataset calculations, use composed metrics.
• Fact filters: filters apply to column facts only, not to facts with an expression.
• Braces in string literals: String values containing { or } are not supported inside expressions.

Best Practices

• Prefer sourceField for simple facts: If a fact maps directly to a database column with no transformation, use sourceField without expression. Expressions are for when you need to transform the value.
• Use NULLIF to prevent division by zero: Write amount / NULLIF(orders, 0) instead of amount / orders.
• Add a format hint for user convenience: Format hints save dashboard users from manually configuring number formatting every time they use the field.
• Keep expressions simple: For calculations across datasets, use composed metrics instead.
• Use descriptive label and description: Help dashboard users understand what the transformed value represents.

Inline Dimension Expressions

Dataset dimensions can also include an expression field for row-level transforms such as date truncation, CASE-WHEN bucketing, and string manipulation. This is the recommended way to add a new calculated dimension to a domain.

Dimension Expressions vs Composed Dimensions

Expression Syntax

Dimension expressions use the same syntax as fact expressions: plain field references and brace notation. See the Expression Syntax section above for details. A dimension expression can use the dataset's identifiers and dimensions; to read another column, such as a fact's column, set the dimension's sourceField to it.

Examples

Date Truncation

dimensions:
  - name: order_month
    label: "Order Month"
    dataType: date
    expression: "DATE_TRUNC('month', order_date)"

Bucketing with CASE WHEN

dimensions:
  - name: status_band
    label: "Status Band"
    dataType: string
    expression: "CASE WHEN status = 'active' THEN 'Active' WHEN status = 'trial' THEN 'Trial' ELSE 'Inactive' END"

String Transform

dimensions:
  - name: upper_region
    label: "Region (Uppercase)"
    dataType: string
    expression: "UPPER(region)"

Concatenation

dimensions:
  - name: full_location
    label: "Full Location"
    dataType: string
    expression: "CONCAT(city, ', ', state)"

Aggregation Behavior

Dimension expressions are row-level: they appear in both SELECT and GROUP BY in the generated SQL. They are not wrapped in any aggregate function.

# This dimension expression:
expression: "DATE_TRUNC('month', order_date)"

# Appears in the generated SQL as:
# SELECT DATE_TRUNC('month', order_date) ... GROUP BY DATE_TRUNC('month', order_date)

Validation Errors

Restrictions

• No aggregate functions: Dimension expressions are row-level. Use SUM, COUNT, etc. only in metrics.
• AS aliasing not supported: Use the dimension's name field instead.
• Single-dataset only: For cross-dataset groupings, use composed dimensions with grain mappings.
• Braces in string literals: String values containing { or } are not supported inside expressions.