Inline Expressions

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

In both cases, inline expressions let you define calculations directly on a field without creating a full calculated metric or calculated dimension.

The optional format field (metrics only) 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 metric onto a card, the format seeds the card's default display settings.

When to Use Inline Expressions vs Calculated 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. At compile time, field names are resolved against the dataset's field inventory.

Referencing Other Metrics

Expression metrics can reference other metrics in the same dataset. If you have a metric amount_usd with expression amount_cents / 100, another metric can build on it:

metrics:
  - name: amount_usd
    expression: "amount_cents / 100"
    sourceField: amount_cents
    dataType: number
  - name: amount_with_tax
    expression: "amount_usd * 1.08"
    sourceField: amount_cents
    dataType: number

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(...) -- makes the expression pre-aggregated

Examples

Currency Conversion

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

Percentage Metric

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

Conditional Logic

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

Rounding

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

Pre-Aggregated Expression

When the expression already contains aggregate functions, Semaphor uses it as-is instead of wrapping it in SUM(...).

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

Format Specification

The format field controls how a 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:

metrics:
  - 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:

metrics:
  - 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
    expression: "profit / NULLIF(revenue, 0)"
    sourceField: profit
    dataType: number
    format:
      type: percent
      decimals: 1
      percentValueMode: fraction

Format Object Reference

Format Precedence

When a user drags a 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 metric definition
3. Visual defaults -- Semaphor's built-in defaults

Aggregation Behavior

Non-Aggregated Expressions

When an expression does not contain an aggregate function (SUM, COUNT, AVG, etc.), Semaphor automatically wraps it in SUM(...) when used as a metric. This mirrors the default behavior for regular numeric metrics.

# This expression:
expression: "amount_cents / 100"

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

Pre-Aggregated Expressions

If the expression already contains aggregate functions, Semaphor uses it as-is.

# This 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

For non-numeric data types (string, date), COUNT is applied instead of SUM.

Validation Errors

When you save a domain template with inline expressions or metric filters, Semaphor validates them. The following error codes apply to inline expressions:

Restrictions

• AS aliasing -- amount_cents AS usd is not supported. Use the metric's name field instead.
• Dimension aggregates -- Dimension expressions must be row-level. Aggregate functions (SUM, COUNT, etc.) are only supported in metric expressions.
• Cross-dataset references -- Inline expressions can only reference fields within the same dataset. For cross-dataset calculations, use calculated metrics.
• Braces in string literals -- String values containing { or } are not supported inside expressions.

Best Practices

• Prefer sourceField for simple metrics -- If a metric 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 metric.
• Keep expressions simple -- For complex multi-step calculations involving multiple datasets, use domain-level calculated metrics instead.
• Use descriptive label and description -- Help dashboard users understand what the transformed metric 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 lets you reshape grouping values directly on a dimension without creating a root-level calculated dimension.

When to Use Inline Dimension Expressions vs Calculated Dimensions

Expression Syntax

Inline dimension expressions use the same syntax as metric expressions -- plain field references and brace notation. See the Expression Syntax section above for details.

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 is the opposite of metric expressions, which default to SUM().

# 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 metric expressions.
• AS aliasing not supported -- Use the dimension's name field instead.
• Single-dataset only -- For cross-dataset groupings, use calculated dimensions with grain mappings.
• Braces in string literals -- String values containing { or } are not supported inside expressions.