Choosing the Right Type

Semaphor offers several ways to define computed fields in a domain template. They look similar but behave differently, and choosing the wrong one leads to unexpected results.

There are two decisions to make:

1. Does it work row by row, or does it aggregate rows? A row-level calculation is a fact (a quantity you aggregate later) or a dimension (a label you group by). An aggregate is a metric.
2. How many datasets? A calculation over one dataset lives on that dataset: a fact, a dimension, or a dataset metric. A calculation that combines datasets is a composed metric with named inputs.

Semaphor checks the first decision for you when you save: an aggregate in a fact or dimension, or a row-level expression in a dataset metric, is refused with a message that says where it belongs.

The Key Question: Are You Measuring or Grouping?

The simplest way to decide: if the result is a number you want to measure, it's a fact or a metric. If the result is a label you want to group by, it's a dimension.

Consider an orders table with these rows:

Profit Margin → Dataset Metric

Profit margin is SUM(amount - cost) / SUM(amount). You want a single number per group: the overall margin for East, the overall margin for West.

The calculation happens after rows are grouped: sum up all amounts for East ($700), sum up all profits for East ($250), then divide. It aggregates orders rows, so it's a dataset metric on orders.

Order Size → Dimension Expression

Order size categorizes each row as "Small" or "Large" based on the amount. You want a label per row that becomes a new way to group.

The calculation happens before grouping: evaluate each row, assign a label, then group by that label. This is a dimension with an expression.

Amount in USD → Fact Expression

If amount is stored in cents and you need dollars, you transform each row before aggregating: amount_cents / 100. The result is still a number you measure, but the transform itself is row-level. This is a fact with an expression, aggregated by its defaultAggregate.

At a Glance

Decision Guide

"I want to compute a number from one dataset"

Use a dataset metric.

Dataset metrics produce aggregated values (totals, averages, ratios, counts) over one dataset's rows. They appear in the VALUES area of a card.

metrics:
  - name: profit_margin
    dataset: orders
    label: "Profit Margin %"
    expression: "(SUM(amount) - SUM(cost)) / NULLIF(SUM(amount), 0) * 100"
    dataType: number
    format:
      type: percent
      decimals: 1

Use a dataset metric when:

• You need a KPI like profit margin, conversion rate, or average order value
• The result should be a single number per group
• Every field it uses belongs to one dataset
• You want to build metrics on each other (profit = revenue - cost, then margin = profit / revenue)

"I want to combine numbers from several datasets"

Use a composed metric.

A composed metric names each input's dataset, so Semaphor can aggregate each dataset on its own before combining the results:

metrics:
  - name: net_revenue
    label: "Net Revenue"
    expression: "{revenue} - COALESCE({returned}, 0)"
    inputs:
      revenue: { metric: revenue, dataset: orders }
      returned: { dataset: returns, field: amount, aggregate: SUM }

Use a composed metric when:

• The calculation uses fields or metrics from more than one dataset
• You want to build on another composed metric

"I want to group by something that doesn't exist as a column"

Use a dimension expression.

Dimension expressions produce row-level values that go into GROUP BY. They create categories, buckets, or transformed labels to group your data by.

datasets:
  - name: orders
    fields:
      dimensions:
        - name: order_size
          label: "Order Size"
          dataType: string
          sourceField: amount
          expression: |
            CASE
              WHEN amount > 1000 THEN 'Large'
              WHEN amount > 100 THEN 'Medium'
              ELSE 'Small'
            END

A dimension expression can use the dataset's identifiers and dimensions. To bucket a fact column such as amount, set the dimension's sourceField to that column, as above.

Use a dimension expression when:

• You need a custom grouping (size buckets, fiscal quarters, region labels)
• You want to truncate dates to a different grain
• You need to combine text fields (full name = first + last)
• The result should appear as a category axis or row header

Domain-level composed dimensions remain available for groupings that need grain mappings across datasets.

"I want to transform a column before it's aggregated"

Use a fact expression.

Fact expressions transform each row, like converting units or applying conditional logic. Cards then aggregate the result with the fact's defaultAggregate or another aggregate.

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

Use a fact expression when:

• The transform is row-level (unit conversion, rounding, conditional logic)
• It applies to one dataset
• You want people to choose how to aggregate it (sum, average, min, max)

Common Mistakes

Defining a ratio as a dimension

What you wrote:

dimensions:
  - name: profit_margin
    dataType: number
    expression: "(amount - cost) / NULLIF(amount, 0)"

What happens: Each row gets its own margin (row 1 = 40%, row 2 = 25%, row 3 = 50%). Semaphor tries to GROUP BY these values, creating a category for every distinct percentage, which is not a meaningful KPI.

Fix: This is a number you want to measure, not a label you want to group by. Use a dataset metric:

metrics:
  - name: profit_margin
    dataset: orders
    dataType: number
    expression: "(SUM(amount) - SUM(cost)) / NULLIF(SUM(amount), 0) * 100"

Putting an aggregate in a fact

What you wrote:

facts:
  - name: average_order_value
    dataType: number
    expression: "SUM(amount) / NULLIF(COUNT(DISTINCT order_id), 0)"

What happens: The save is refused with aggregate_in_row_field. A fact is a row-level quantity that cards aggregate; an expression that already aggregates can't be aggregated again.

Fix: Move it to metrics as a dataset metric:

metrics:
  - name: average_order_value
    dataset: orders
    dataType: number
    expression: "SUM(amount) / NULLIF(COUNT(DISTINCT order_id), 0)"

Putting a row-level expression in a metric

What you wrote:

metrics:
  - name: amount_usd
    dataset: orders
    dataType: number
    expression: "amount_cents / 100"

What happens: The save is refused with row_expression_in_dataset_metric. The expression works row by row, so it has no single value per group.

Fix: Add it as a fact (to aggregate) or a dimension (to group by):

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

Using a composed metric for one dataset

What you wrote:

metrics:
  - name: amount_usd_total
    expression: "SUM({amount_cents}) / 100"
    inputs:
      amount_cents: { dataset: orders, field: amount_cents }

What happens: It works, but it's more complex than necessary: named inputs for a calculation that only touches orders.

Fix: Use a dataset metric. It's simpler and keeps the definition next to its dataset:

metrics:
  - name: amount_usd_total
    dataset: orders
    dataType: number
    expression: "SUM(amount_cents) / 100"

Mixing datasets in a dataset metric

What you wrote:

metrics:
  - name: net_revenue
    dataset: orders
    dataType: number
    expression: "SUM(amount) - SUM(returns.amount)"

What happens: The save is refused with dataset_metric_references_other_dataset. A dataset metric aggregates one dataset's rows.

Fix: Use a composed metric whose inputs name each dataset (see Decision Guide).

Summary