Sit down, get your tea. You have written your first DAX measure, it shows the right total in a card visual, and then you drop it in a table by region and the numbers look nonsensical. Nothing is broken. DAX is doing exactly what it is designed to do, and the thing you are missing is the idea of context. Two kinds of it, to be exact, and most of the confusion comes from mixing them up.
A tiny table to hold in your head
Take a Sales table with four rows. Chennai: quantity 2 at ₹500, and quantity 1 at ₹1,200. Pune: quantity 3 at ₹400, and quantity 1 at ₹1,000. Work out each line by hand: ₹1,000, ₹1,200, ₹1,200, ₹1,000. The grand total is ₹4,400, with ₹2,200 for each city. Keep these numbers; every example below uses them.
Row context: “I am standing on this one row”
Row context means DAX is visiting rows one at a time. The easiest place to see it is a calculated column. If you add a column called Amount with the expression Quantity times Price, DAX walks down the table and, for each row, multiplies that row’s quantity by that row’s price. You never wrote a loop, but that is what happened. Inside the column, a bare reference to Sales[Quantity] means “the quantity on the row I am currently on”.
Row context does not filter anything. It does not know that Chennai exists as a group. It is just a pointer to a current row. This is why a bare column reference does not work in a measure: a measure has no current row, so DAX would not know which quantity you meant. You must wrap the column in an aggregation such as SUM.
Filter context: “only these rows are visible”
Filter context is the set of filters currently applied to the model. Put Region on the rows of a table visual and, for the Chennai row, the visual silently applies a filter of Region equals Chennai before evaluating your measure. A slicer on year adds another filter. A page-level filter adds another. The measure SUM(Sales[Amount]) then adds up whatever rows survive. Same formula, three answers: ₹4,400 in the card, ₹2,200 against Chennai, ₹2,200 against Pune.
Notice that the formula never changed. Its result depends on the filters around it, and that is the whole point of a measure. You write it once and every cell of every visual is a different evaluation.
The mistake that shows the difference
Suppose you skip the Amount column and try a measure that multiplies SUM(Sales[Quantity]) by SUM(Sales[Price]). Total quantity is 7, total price is ₹3,100, and the product is ₹21,700, nowhere near ₹4,400. For Chennai alone you get 3 times ₹1,700, which is ₹5,100 instead of ₹2,200. The measure multiplied two totals, when what you wanted was a total of products.
The fix is an iterator. SUMX takes a table and an expression, creates a row context on that table, evaluates the expression on every row, then adds up the results. SUMX(Sales, Sales[Quantity] * Sales[Price]) gives ₹4,400 overall and ₹2,200 per city, with no helper column at all. The “X” functions (AVERAGEX, MAXX, COUNTX) are all row-context generators sitting inside a filter context. That combination, an outer filter context and an inner row context, is where most real DAX lives.
CALCULATE changes the filter context
CALCULATE evaluates an expression after modifying the current filters. A measure like CALCULATE([Total Sales], Sales[Region] = "Pune") will return ₹2,200 no matter which row of the visual it is in, because the Pune filter replaces whatever filter was on Region. Filters on other columns, such as year, stay in place unless you remove them too.
The common use is a share of total. Wrap the denominator with ALL(Sales[Region]), which removes the region filter, and each city’s sales divided by the all-region sales gives 50 percent for Chennai and 50 percent for Pune. ALL clears filters on the column you name, ALLEXCEPT clears everything except the columns you list, and REMOVEFILTERS does the same job as ALL but reads more clearly when used only as a filter modifier.
Context transition, the part that surprises people
Here is the subtle bit. When CALCULATE runs while a row context exists, it takes the values of the current row and turns them into filters. That conversion is called context transition. It is why a measure referenced inside an iterator behaves differently from a plain column: each row’s values become a filter, and the measure is evaluated against that tiny slice.
Every measure reference has an invisible CALCULATE wrapped around it, so writing SUMX(Customers, [Total Sales]) gives you sales per customer, summed, because on each customer row the measure sees that customer as a filter. It is powerful and it is also slow if the table you iterate has millions of rows, since the measure is evaluated once per row. Iterate over a small table (the distinct regions, say) rather than the big fact table whenever you can.
A habit worth building
When a number looks wrong, do not stare at the formula. Ask two questions of the cell. Which filters are active here, and is there a row context at this point? Write down the surviving rows by hand for a small case like the four-row table above, and compare with what the visual shows. Nine times out of ten the discrepancy is a filter you forgot (a slicer, a relationship direction) or an aggregation done before multiplying instead of after.
Relationships matter here too. Filter context travels from a dimension to a fact table along relationships, which is why a Region column in a separate dimension filters Sales correctly in a star schema. If a relationship is inactive or points the wrong way, the filter simply does not arrive, and your measure shows the grand total on every row. That symptom alone, the same number repeated down a table, is almost always a missing filter path.
Figures and names in the examples are illustrative.
Leave a Reply