How to add a filter to a metric in dbt (Dimension() in filter)

A filter turns a metric into a narrower one: revenue from Italy, revenue from shipped large orders, revenue from repeat customers. It is a SQL condition in which every dimension is written through a Jinja object such as {{ Dimension('customer__country') }}, so MetricFlow knows what to join.

Copy and paste

    metrics:
      - name: italy_revenue
        type: simple
        agg: sum
        expr: amount
        filter: "{{ Dimension('customer__country') }} = 'IT'"

      - name: large_shipped_revenue          # several conditions in one string
        type: simple
        agg: sum
        expr: amount
        filter: "{{ Dimension('order__status') }} = 'shipped' and {{ Dimension('order__order_size') }} = 'large'"

      - name: dach_or_france_revenue         # IN, OR and parentheses are plain SQL
        type: simple
        agg: sum
        expr: amount
        filter: "{{ Dimension('customer__country') }} in ('DE', 'AT', 'CH', 'FR')"

      - name: revenue_since_2026             # a time dimension needs a grain
        type: simple
        agg: sum
        expr: amount
        filter: "{{ TimeDimension('order__order_date', 'day') }} >= '2026-01-01'"

      - name: repeat_customer_revenue        # a metric as a dimension
        type: simple
        agg: sum
        expr: amount
        filter: "{{ Metric('order_count', group_by=['customer']) }} >= 4"

mf query --metrics revenue,italy_revenue,large_shipped_revenue,dach_or_france_revenue,revenue_since_2026,repeat_customer_revenue \
  --group-by metric_time__year

The objects

Dimensions are always written with their entity, customer__country, exactly as in a query. The filter can use dimensions from joined models: italy_revenue is defined on orders and filters on a column of customers.

Metrics as dimensions

Metric('order_count', group_by=['customer']) computes the order count per customer, joins it to every order and filters on it. repeat_customer_revenue therefore keeps all the orders of Anna, Ben and Chiara, who have four or more orders in total, including their first ones. That is the Semantic Layer version of “revenue from customers with at least four orders”, which would otherwise need a model in dbt.

group_by takes exactly one entity, and the inner metric is computed over all time, not within the period of each row.

YAML quoting

• Wrap the whole filter in double quotes, and string values inside it in single quotes: "{{ Dimension('order__status') }} = 'shipped'". Without the outer quotes, YAML reads {{ as the start of a mapping and the parse fails.
• Write several conditions in one string with and, or and parentheses. dbt Core 1.12 also accepts a YAML list of conditions under filter:, but dbt v2 2.0.9 rejects it: YAML error: metrics[15].filter: invalid type: sequence, expected a string.
• The comparison is SQL for your warehouse: date literals, in lists and string functions follow its dialect.

Filters on derived and ratio inputs

Inputs of ratio, derived and cumulative metrics accept a filter too, written on the input:

metrics:
  - name: italy_share_of_revenue
    type: derived
    expr: italy / total
    input_metrics:
      - name: revenue
        alias: italy
        filter: "{{ Dimension('customer__country') }} = 'IT'"
      - name: revenue
        alias: total

The same metric used twice with different filters needs an alias for each, which expr then uses.

Filter in the metric or in the query

A filter in the YAML defines a new metric that every tool sees the same way. A filter at query time (--where in dbt sl query, or a filter in a BI tool) is for one analysis; see how to filter a metric query. If people keep applying the same filter, it has become a metric.

Tested with dbt Core 1.12 and MetricFlow 0.213 on DuckDB; the filter forms were also parsed with dbt v2 2.0.9.