“Returned orders” is order_count with a condition. The Semantic Layer gives you three ways to write it: a filter on the metric, a case expression in expr, or sum_boolean. They return the same counts where there are matching rows, and differ in what they can see and in what they return where there are none.

Copy and paste

A filter on the metric (can use dimensions of joined models):

      - name: returned_orders
        type: simple
        agg: count
        expr: 1
        filter: "{{ Dimension('order__status') }} = 'returned'"
      - name: smb_orders
        type: simple
        agg: count
        expr: 1
        filter: "{{ Dimension('customer__segment') }} = 'SMB'"

A case expression (only the model’s own columns):

      - name: returned_orders_case
        type: simple
        agg: sum
        expr: "case when status = 'returned' then 1 else 0 end"

A boolean expression:

      - name: late_orders
        type: simple
        agg: sum_boolean
        expr: "shipped_date - order_date > 3"

Distinct values that match:

      - name: customers_with_a_return
        type: simple
        agg: count_distinct
        expr: "case when status = 'returned' then customer_id end"

A rate, in a separate metrics file:

metrics:
  - name: return_rate
    type: ratio
    numerator: returned_orders
    denominator: order_count

Example

mf query --metrics order_count,smb_orders,returned_orders,return_rate --group-by metric_time__year
metric_time__year      order_count    smb_orders    returned_orders    return_rate
-------------------  -------------  ------------  -----------------  -------------
2025-01-01T00:00:00             12             7                  1      0.0833333
2026-01-01T00:00:00              7             4                  1       0.142857

Filter or expression

A filter becomes a WHERE clause in the subquery that computes the metric, after MetricFlow has joined whatever the filter needs. That is why smb_orders can filter on customer__segment, a dimension of another model:

  ...
  LEFT OUTER JOIN
    "shop"."main"."customers" customers_src_10000
  ON
    subq_1.customer = customers_src_10000.customer_id
) subq_6
WHERE customer__segment = 'SMB'

An expr is evaluated on the rows of the model itself, so it can only use that model’s columns. In exchange it is plain SQL, which is handy for conditions on several columns at once.

NULL or zero

The difference shows on groups with no matching rows. Germany has no returned orders:

customer__country      returned_orders    returned_orders_case    return_rate
-------------------  -----------------  ----------------------  -------------
DE                                                           0
IT                                   2                       2       0.166667

The filtered metric has no rows to count in Germany and returns NULL; the case version adds up zeros and returns 0. The return rate inherits the NULL. To show 0 in both cases, add fill_nulls_with: 0 to the filtered metric; see how to show zero instead of NULL.

sum_boolean

sum_boolean counts the rows where expr is true. It reads better than a case for flags, and the expression can compare columns, as shipped_date - order_date > 3 does. Write the comparison in your warehouse’s SQL: on Snowflake, datediff('day', order_date, shipped_date) > 3.

count_distinct with a condition

A case without else returns NULL for the rows that don’t match, and count_distinct ignores NULLs. customers_with_a_return therefore counts the customers with at least one returned order: 2 in the example, Anna and Chiara.

Tested with dbt Core 1.12 and MetricFlow 0.213 on DuckDB.

References