Day 8 — Filter context and row context
The two contexts. Almost every confusing DAX result comes from mixing them up.
Filter context — what is currently visible
Filter context is the set of filters in force when a measure runs. It comes from three places, and they stack:
- The visual — the row and column a cell sits in
- Slicers and page filters
- CALCULATE, inside the measure itself
One measure, four different answers, because each row supplied a different filter context. The measure never mentions Region at all.
Why the total row is not the sum of the rows
This causes more confused emails than anything else in Power BI.
Sales adds up because addition works that way. Margin % does not, because ratios do not. Nothing is broken.
Row context — one row at a time
Row context is completely different: it means "we are currently sitting on one particular row and can read its columns". Measures do not have it by default, which is why this fails:
The error is the point. In a measure, Sales[Amount] is meaningless — which of the 48 amounts did you mean? A calculated column has row context automatically. A measure has to create it, with an iterator.
Iterators create row context
The X functions — SUMX, AVERAGEX, MINX, MAXX, COUNTX — walk a table row by row, evaluate an expression on each row, and aggregate the results.
The first two agree, and so do the last two. Inside SUMX, Sales[Amount] now means something — the amount on this row.
When SUMX and SUM genuinely differ
SUM(Qty) * SUM(Price) multiplies two totals — 437 units times the sum of all prices. Meaningless.
SUMX(Sales, Sales[Qty] * RELATED(Product[Price])) multiplies on each row and then adds. Correct.
Whenever a calculation multiplies or divides two columns, it must happen row by row.
The middle line is off by a factor of thousands. It produces a number, it produces no error, and in a report nobody would notice.
RELATED — reaching across a relationship
Inside a row context on Sales, RELATED follows the relationship to fetch a column from a dimension.
Averages are the classic trap
Three different averages, three different questions. The first two happen to match here because every row has an amount. The third is a different quantity entirely — revenue per unit, not per order. Being clear which one a stakeholder is asking for is most of the job.
Try these yourself
- Explain in one sentence why a Margin % total row is not the sum of the rows above it.
- Write a measure using SUMX that totals Qty times 100.
- Explain why SUM(Sales[Qty]) * SUM(Product[Price]) is wrong.
- Use RELATED inside SUMX to total the Accessories revenue.
- Give one example of a calculation that must use an iterator.