Why this matters
As a BI Developer, your DAX must return correct numbers in every visual, slicer, and drillthrough. That reliability depends on understanding two pillars: row context (how DAX evaluates one row at a time) and filter context (how visuals, slicers, and functions narrow the data used by a measure). Mastering these lets you build accurate KPIs, time-intelligence, and percent-of-total metrics that hold up under any filter combination.
- Real tasks this unlocks:
- Percent of total by slicer selection
- Year-over-year comparisons that ignore some filters but respect others
- Dynamic rankings (Top N) that update by segment or date
- Row-level calculations (margins, unit price) that aggregate correctly
Concept explained simply
Row context: DAX looks at one row at a time. Typical in calculated columns and iterators like SUMX. You can reference other columns in the same row directly.
Filter context: The set of filters applied before an expression is evaluated. Comes from report visuals (rows/columns/slicers) and from DAX functions like CALCULATE, ALL, KEEPFILTERS, and CROSSFILTER.
Mental model: Zoom lens + conveyor belt
Imagine a conveyor belt of rows (row context). Each time you pick up a row, you can do math with just that row's data. Now place a zoom lens on your table (filter context). The lens narrows what you can see: only certain dates, products, or regions. Measures use whatever the lens shows. CALCULATE swaps or adds lenses to change what is visible before doing the math.
Key ideas and rules
- Calculated columns evaluate with row context; visuals later aggregate their results.
- Measures evaluate with filter context; no row context unless introduced by iterators or context transition.
- CALCULATE changes the filter context and performs context transition if a row context exists.
- Iterators (SUMX, AVERAGEX, etc.) create a row context that iterates a table while returning a single scalar result in filter context.
- ALL/ALLEXCEPT/ALLSELECTED/REMOVEFILTERS expand or reshape filter context; VALUES returns a one-column table of distinct values from the current filter context.
- Relationships propagate filters from dimension tables to fact tables; expanded tables matter when evaluating filters.
Worked examples
Assume a simple model:
- Products(ProductKey, ProductName, UnitCost)
- Sales(Date, ProductKey, Quantity, SalesAmount)
- Calendar(Date)
- Relationships: Products 1->* Sales, Calendar 1->* Sales
Example 1 — Row context in a calculated column
-- Calculated column in Sales
Sales[Line Margin] = Sales[SalesAmount] - Sales[Quantity] * RELATED(Products[UnitCost])
Why it works: Calculated columns have row context, so each row can directly use its own Quantity and SalesAmount, plus RELATED to pull the current row's Product UnitCost.
Aggregation later: When you sum Sales[Line Margin] by Product, the visual applies a filter context per product and aggregates pre-computed row values that match.
Example 2 — Filter context in a measure
Total Sales = SUM(Sales[SalesAmount])
Sales Qty = SUM(Sales[Quantity])
These measures respect whatever filter context exists (e.g., selected Product, Month). In a matrix by Product, each product cell has its own filter context; the measure recomputes per cell.
Example 3 — CALCULATE modifies context (percent of total)
Total Sales = SUM(Sales[SalesAmount])
Sales % of All Products =
DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Products)))
Inside a visual by Product, the numerator uses the product’s filter context. The denominator removes product filters with ALL(Products), giving the grand total in the current report context (dates, regions still respected unless also removed).
Example 4 — Iterators create row context
Total Margin (measure) =
SUMX(
Sales,
Sales[SalesAmount] - Sales[Quantity] * RELATED(Products[UnitCost])
)
SUMX iterates rows of Sales (row context per row), evaluates the expression, and returns a single scalar that still respects external filter context (e.g., Date slicers, Product selection).
Step-by-step practice
- Create basic measures (Total Sales, Sales Qty) and place them in a table by Product. Observe how slicers change results. Note: you are seeing filter context at work.
- Add a calculated column (Line Margin). Then add a measure Total Margin using SUMX. Compare behavior when filtering by Product and Date.
- Build Sales % of All Products using CALCULATE + ALL(Products). Verify that product slicers no longer affect the denominator but date slicers still do.
- Experiment with ALLEXCEPT(Products, Products[Category]) to keep category while removing product filters.
Exercises
Complete the exercises below. Everyone can take them for free. If you are logged in, your progress will be saved automatically.
Exercise 1 — Row vs filter context with margins
Use this tiny dataset to validate your DAX (you can paste into a small table if needed):
Products
ProductKey | ProductName | UnitCost
1 | A | 5
2 | B | 7
Sales
Date | ProductKey | Quantity | SalesAmount
2024-01-01 | 1 | 2 | 20
2024-01-02 | 1 | 1 | 10
2024-01-03 | 2 | 3 | 30
2024-01-03 | 2 | 1 | 12
- Create Sales[Line Margin] (calculated column): SalesAmount - Quantity * RELATED(Products[UnitCost]).
- Create Total Margin (measure) using SUMX over Sales with the same expression.
- Place ProductName in rows; show Total Margin and the Sum of the column Sales[Line Margin]. Compare.
- Checklist:
- Calculated column created with RELATED()
- Measure uses SUMX over Sales
- Totals per product match expected values
Exercise 2 — Percent of all products with CALCULATE
- Create Total Sales = SUM(Sales[SalesAmount]).
- Create Sales % of All Products = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Products))).
- Place ProductName as rows and show both measures. Confirm percentages for A and B using the dataset above.
- Checklist:
- ALL(Products) removes product filters in the denominator
- Percentages match manual calculation
- Date filters still affect both numerator and denominator unless removed
Common mistakes and self-checks
- Mistake: Putting business logic into calculated columns when it needs to be dynamic by slicer. Self-check: Does this need to recalc per selection? If yes, make it a measure (possibly with an iterator).
- Mistake: Expecting a measure to ignore a slicer without using ALL/REMOVEFILTERS. Self-check: Inspect your denominator. Does it explicitly modify filter context?
- Mistake: Using SUM over a calculated column when precision depends on relationships/filters. Self-check: Could SUMX over base columns be more robust?
- Mistake: Forgetting context transition. Self-check: When CALCULATE appears in a row context (e.g., inside iterators), it transitions row context into an equivalent filter context.
Mini challenge
Create a measure Sales % of Category that divides product sales by the total for its category, even when a product is selected. Hint: use ALLEXCEPT(Products, Products[Category]). Verify that product slicers don’t change the denominator but category slicers do.
Who this is for
- Aspiring and current BI Developers building Power BI models
- Analysts moving from SQL/Excel to DAX
- Anyone troubleshooting unexpected totals in visuals
Prerequisites
- Basic Power BI model with dimensions and a fact table
- Intro DAX: measures vs calculated columns
- Comfort reading simple SUM/SUMX/CALCULATE
Learning path
- Re-create the sample model and measures from the examples.
- Practice context changes with CALCULATE + ALL/ALLEXCEPT.
- Add time-intelligence filters (e.g., DATESINPERIOD) to see compound contexts.
- Validate with manual checks on small datasets.
Practical projects
- Category percent-of-total dashboard that honors Date and Region but ignores Product
- Margin analysis report with product-level drilldown and dynamic Top N
- QA page to compare SUM over column vs SUMX expression results for data sanity
Next steps
- Extend your measures with ALLSELECTED to reflect user selections in totals
- Introduce KEEPFILTERS for additive filter behavior
- Layer time intelligence once context fundamentals feel natural
Take the quick test
Test is available to everyone for free. Log in to save your progress and resume later.