Module 5: Filter context and CALCULATE · Lesson 5.5 · about 18 min
Context transition
When CALCULATE runs inside a row context, the current row becomes a filter. That's why a measure inside SUMX gives per-row answers. Watch the two versions side by side and learn the patterns that rely on it.
Module 5 · about 18 minutes · read first: 5.2 and 4.3. You’ll be able to explain context transition and use it for per-item calculations.
1. Why this matters
Context transition is the idea that separates people who copy DAX from people who write it. It explains why measures inside iterators work, why some formulas are slow, and how to answer “best day”, “average per customer” and “how many products sold more than X”.
2. Two versions, side by side
Run both, split by Category. Without repeats the same number for every product; With gives the right total. Trace each (put it last) to see why.
Press Run to see the result.
Trace With on the Home row: SUMX goes through the 3 Home products, and for each one CALCULATE turns the product row into a filter, so [Total Sales] gives that product’s sales. Now move Without to the last line and trace it: SUM ignores the product row and sees all Home sales each time, so the answer is 3 × Home’s sales.
3. The rule
- A row context (from an iterator or a calculated column) gives you a current row, but filters nothing.
- CALCULATE inside a row context performs context transition: the current row’s values become filters (for a table row, effectively “this row”).
- Every measure reference like
[Total Sales]is wrapped in an invisible CALCULATE, so it always performs context transition.
4. Patterns that rely on it
Write Best Day: the highest daily sales in the current context, with MAXX over the visible dates.
Press Run to see the result.
Total Sales = SUM(Sales[Amount])
Best Day = MAXX(VALUES('Date'[Date]), [Total Sales]) Write Average Daily Sales with AVERAGEX over the dates. Compare it with Total Sales ÷ number of days. Why are they different for Forks Market?
Press Run to see the result.
Total Sales = SUM(Sales[Amount])
Average Daily Sales = AVERAGEX(VALUES('Date'[Date]), [Total Sales]) AVERAGEX(VALUES('Date'[Date]), [Total Sales]) evaluates sales for each date and averages them. Days with no sales give blank, which AVERAGEX skips, so it’s the average over days with sales. Per Calendar Day divides by every day in the Date table (731), so it’s much lower for Forks Market, which only existed for part of 2025. Choose the one that matches the question.
Principle
Principle: for an iterator over table with expression :
The measure is evaluated once per row , with the row added as a filter. Without CALCULATE (a bare SUM), the part is missing and every row sees the same rows.
Cost: context transition on a large table is expensive (a new filter context per row). Iterate small tables (dates, products, customers), not the fact table, when you call measures inside an iterator.
5. Worked example
In a Product calculated column, count each product’s order lines. Product[Lines] = COUNTROWS(Sales) gives 7,328 for every product: a calculated column has a row context but no filter context, so COUNTROWS sees all of Sales. Product[Lines] = CALCULATE(COUNTROWS(Sales)) gives each product’s own count: CALCULATE turns the product row into a filter, which flows to Sales. Same for COUNTROWS(RELATEDTABLE(Sales)), which is CALCULATETABLE in disguise.
6. Practice
- Write
Worst Day(the lowest daily sales among days with sales). - Why does
SUMX(Store, SUM(Sales[Amount]))give 5 × the total when no store is filtered? - Write
Average Sales per Productfor the current context.
Answers
MINX(VALUES('Date'[Date]), [Total Sales])(blank days are skipped).- SUM ignores each store row (no context transition), so it adds the full total once per store.
AVERAGEX(VALUES(Product[Product]), [Total Sales]).
Check yourself
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. Northbay Goods is a made-up shop and its numbers are generated. Power BI changes every month: menu names can move, so check Microsoft Learn if a button is not where the lesson says. The practice editor runs in your browser; nothing you type is sent anywhere.