Three filter layers

Definition: inside the metric contract

Query: this plan only

Display: which members are returned

Intersection: a query cannot widen a definition

Ratio: numerator and denominator can differ

The same predicate constrains different objects at different layers

The same East China predicate can return different numbers in a metric definition and in the current query. A metric filter is part of that metric contract. It applies before the metric is aggregated, and a query cannot widen it. A query filter belongs only to this plan and intersects the metric filter. A predicate on a dimension still needs a second distinction: it either limits fact rows before aggregation, or it only chooses which members appear in the result.

A phrase such as region equals East China is therefore not one switch. State whether it changes the metric definition, this request, or the projection. If the layer is undeclared, the engine should not choose one. Additive totals, ratios, and multi-metric alignment will not agree.

A metric filter is applied before that metric aggregates

A metric filter always travels with the metric. East China sales includes region equals East China even when the question says nationwide. The predicate limits rows entering that aggregation. It does not subtract from a national total after the fact. A nationwide sales figure is a different metric, one that does not carry this filter.

dbt lets a metric carry a filter. On a ratio, the filter can apply to both the numerator and the denominator, or to only one of them. Each input of a derived metric can carry its own filter. East China on the numerator alone does not filter the denominator. Copying the same text into the query predicate does not create that structure.

A query filter changes only this plan

A query filter comes from this question, report slice, or API parameter. It is not written back into the metric. The next query without the predicate still uses the original metric. Where both predicates exist, the engine intersects them. If the metric is already East China and this query asks for North China, the effective row set is empty. The result is empty or a declared zero, not the union of the two regions.

Push the query filter into each base metric before that metric aggregates. A request for sales and East China sales should place the region predicate inside both aggregations. Computing national figures and filtering only the outer query feeds ratios and cross-fact alignment with mixed intermediate results. Across fact tables, the constraint belongs inside each aggregation, and the aggregated sets are aligned afterward.

Say whether a dimension filter constrains or only displays

A dimension predicate has two uses. A constraint is a pre-aggregation predicate. It changes the measure and behaves as a query filter. A display filter only limits which members are returned. It does not change a measure that was already computed. A total calculated before the display filter can still include hidden members. If the user expects the number to change, the predicate must be a constraint, not a legend toggle.

Three precedence rules are enough. First, the definition filter always applies, and a query cannot replace it with a wider set. Second, the query constraint intersects the definition filter and is applied before each metric aggregates. Third, a display filter does not enter the measure. When it conflicts with a constraint, keep the constrained aggregate, then choose visible members. Because a filter changes the rows that enter the grain, declare it with the grain so the same metric name is not reused for two different row sets.

A made-up ratio shows where East China was applied

The figures here are an example. Three sales rows are East China 100, East China 50, and North China 80, for a total of 230. Sales has no definition filter. East China sales filters to that region and is 150. If the East China share ratio filters only the numerator, the result is 150/230.

If this query adds East China as a constraint on both the numerator and the denominator, sales becomes 150, East China sales stays 150, and the share becomes 150/150. Moving the same words from the numerator definition to the whole query turns a partial share into the whole. If the predicate is only a display filter, the denominator can remain national, the share can remain 150/230, and only the East China member is shown. Each plan is coherent. Mixing them is not.

One outer predicate, or a predicate per metric

Path one collects every predicate into a single outer filter. The SQL is short, and one additive total can look right. The path cannot express “filter the numerator only,” cannot stop a query from widening a definition, and cannot tell a display filter from a constraint. A multi-metric query then shares one predicate, and the ratio is rewritten with it.

Path two compiles a predicate per metric. For each base metric, the effective predicate is the intersection of the definition filter and the query constraint, pushed into that metric aggregation. A derived metric states whether the query constraint applies to every input, only the numerator, or only the denominator. Display filters stay on the projection after alignment. Semantic models that separate measures and dimensions, with metric filters referencing dimensions, can supply this plan. The generated statement still has to be checked for predicate placement. The plan is longer, and acceptance looks at each aggregate node, not at one cell.

Counterexamples and acceptance checks

Counterexample one stores East China inside the metric but labels the result sales, so a national question returns a regional number. Counterexample two unions a North China query filter with an East China metric filter. Counterexample three applies the outer filter after the join, so the denominator has already been reduced to the numerator rows and the share becomes the whole. Counterexample four hides North China in the display while the total still includes it, with no predicate explaining the gap.

Run the same example as a definition filter, a query constraint, their intersection, a numerator-only ratio, a ratio filtered on both inputs, and a display filter that does not change a total already computed. An empty intersection stays empty or uses the declared zero. It does not fall back to the unfiltered metric. A second query without East China must not reuse the first query predicate. The plan text should mark each predicate as definition, query, or display.

BuildTable.ai boundary

Turn East China into three distinguishable plans. Check sales, East China sales, and the share against the three-row example, then attach a real region dimension. Test renamed regions, unknown members, and several keys for one label. For cross-fact metrics, confirm that the constraint was pushed into each aggregation rather than appearing only in the final projection.

BuildTable.ai can be considered as an entry point for semantic modeling. This article does not show that the current release implements metric, query, and display filter precedence, cross-fact aggregation before alignment, or semantic version pins. Verify the layer of each predicate against the actual model, the query plan, and the acceptance fixtures.

Why does the same East China predicate change the result when it moves from the metric into the query?