Power BI Advanced - Advanced DAX Evaluation
Advanced DAX Evaluation
Understand how DAX evaluates measures through filter context, row context, context transition, iterators, relationships and modified evaluation environments.
Module overview
Writing valid DAX syntax is only the beginning. To create dependable advanced measures, a developer must understand the environment in which each expression is evaluated.
The same measure can return different results in a card, table, matrix, chart, tooltip or total row because each location provides a different filter context.
Iterators add another layer by evaluating an expression one row at a time. The CALCULATE function can then modify filters or transform a current row into filters that affect a calculation.
Learning outcomes
After completing this module, participants will be able to:
- Explain filter context and row context
- Identify the context affecting a measure result
- Explain how relationships propagate filters between tables
- Use iterators to evaluate row-level expressions
- Explain and apply context transition
- Modify filter context using CALCULATE
- Preserve filters using KEEPFILTERS
- Use variables to clarify evaluation behaviour
- Interpret totals generated under a different context
- Debug measures using temporary diagnostic calculations
1. Measures are evaluated in context
Consider a basic measure:
The formula is simple, but its result depends on the current filter context.
| Visual location | Possible filter context | Result |
|---|---|---|
| Report card | Current page and report filters | Total sales permitted by those filters |
| Category row | Current category plus report filters | Sales for that category |
| Month column | Current month plus report filters | Sales for that month |
| Category and month cell | Current category, month and report filters | Sales for that category and month |
| Total row | No individual category row filter | Measure evaluated again for the total context |
2. Filter context
Filter context is the collection of filters that determines which model rows are visible to an expression.
Sources of filter context
Report design
- Rows and columns
- Axes and legends
- Slicers
- Visual filters
- Page filters
- Report filters
Model behaviour
- Relationships
- Cross-filter direction
- Row-level security
- Active relationship paths
DAX expressions
- CALCULATE
- FILTER
- ALL
- REMOVEFILTERS
- KEEPFILTERS
- TREATAS
Example filter context
A matrix cell might be evaluated under these filters:
- Year = 2026
- Region = Central
- Product Category = Bikes
- Channel = Online
The Total Sales measure sees only FactSales rows reachable under that combined context.
3. Filter propagation through relationships
Filters applied to dimension tables normally propagate to fact tables through active relationships.
Category = Bikes
ProductKey
Bikes only
A developer should therefore analyse both the DAX formula and the semantic model when a measure returns an unexpected result.
4. Row context
Row context identifies the current row being evaluated. It is common in calculated columns and iterator functions.
Calculated-column example
For each row, DAX obtains Quantity and UnitPrice from that current FactSales row.
Row context can be nested
When one iterator operates inside another iterator, multiple current rows may exist. Each iterator introduces its own row context.
5. Filter context versus row context
Filter context
Determines the set of rows currently visible to a calculation.
Commonly created by
- Report visuals
- Slicers and filters
- Relationships
- CALCULATE
Row context
Identifies the current row evaluated by an expression.
Commonly created by
- Calculated columns
- SUMX
- AVERAGEX
- FILTER
- ADDCOLUMNS
| Question | Filter context | Row context |
|---|---|---|
| What does it identify? | A permitted set of model rows | A current row |
| Does it affect aggregations? | Yes | Not automatically |
| Does it propagate through relationships? | Yes, according to the relationship path | Not until converted into filter context |
| Typical expression | CALCULATE([Total Sales], DimProduct[Color] = "Blue") | SUMX(FactSales, FactSales[Quantity] * FactSales[Price]) |
6. Iterator functions
Iterator functions evaluate an expression once for every row of a table and then aggregate or otherwise process the individual results.
Common iterators
| Iterator | Purpose |
|---|---|
| SUMX | Evaluates and sums one result per row |
| AVERAGEX | Evaluates and averages one result per row |
| MINX | Returns the smallest evaluated row result |
| MAXX | Returns the largest evaluated row result |
| COUNTX | Counts nonblank numerical expression results |
| FILTER | Evaluates a condition for each row and returns a table |
| ADDCOLUMNS | Adds evaluated expressions to each row of a table |
Revenue calculated at transaction-line level
The measure performs three conceptual steps:
FactSales is first restricted by the current filter context.
SUMX creates row context and multiplies Quantity by UnitPrice.
SUMX adds the evaluated line amounts.
7. SUM versus SUMX
| Requirement | Recommended function |
|---|---|
| Add one existing numeric column | SUM |
| Calculate an expression for every row and add the results | SUMX |
| Sum a stored SalesAmount column | SUM(FactSales[SalesAmount]) |
| Sum Quantity multiplied by UnitPrice | SUMX(FactSales, Quantity * UnitPrice) |
8. Context transition
Context transition converts the current row context into filter context.
The CALCULATE function without filter arguments can perform this transition when an aggregation expression must be evaluated inside row context.
ProductKey = 101
ProductKey filter
Product 101 only
Example inside an iterator
VALUES produces the visible product keys. SUMX establishes a current product row. CALCULATE converts that row into a filter before evaluating Total Sales.
9. CALCULATE modifies filter context
CALCULATE evaluates a scalar expression under modified filter context.
Add a new filter
If Product Color is not already filtered, the Blue filter is added.
Replace an existing filter
If Product Color is already filtered, the filter argument normally replaces the existing filter on that column.
Remove a filter
Preserve other filters
Removing product filters does not automatically remove date, customer, region or security filters.
10. Preserve existing filters with KEEPFILTERS
KEEPFILTERS changes how a CALCULATE filter interacts with an existing filter on the same column.
Default replacement behaviour
Intersection behaviour
If a report user selects Red only:
- Blue Sales may replace Red with Blue.
- Blue Sales Within Selection returns blank because Red and Blue do not intersect.
11. Boolean filters versus FILTER
When possible, Microsoft recommends using Boolean filter expressions directly in CALCULATE.
Preferred simple filter
FILTER required for more complex logic
The second example evaluates a measure for each visible product. This requires table iteration and cannot be expressed as a simple column comparison.
12. CALCULATETABLE
CALCULATETABLE modifies filter context in the same general way as CALCULATE, but it returns a table rather than a scalar value.
Example
CALCULATETABLE returns the FactSales rows visible under the Blue Product filter. SUMX then evaluates revenue for each returned row.
13. Expanded filtering effects
In a relational semantic model, filters can affect more than the column explicitly mentioned in a formula. Active relationships allow filters to propagate between related tables.
For example, removing filters from a fact table can have wider effects than removing a filter from one fact-table column. Similarly, filtering a dimension can restrict related fact rows even though the DAX formula never directly references the fact key.
Prefer targeted filter modification
Instead of removing every filter from a whole table:
remove only the required column filter when appropriate:
The second expression preserves other product filters, such as Brand or Color, unless those filters are affected through other logic.
14. Nested CALCULATE expressions
Advanced measures sometimes contain CALCULATE expressions inside other expressions. Each layer creates or modifies the context in which the next expression is evaluated.
Example
Both variables begin from the measure's current context, but each applies a different modification to Product Color.
15. Variables and evaluation
A DAX variable stores the result of an expression evaluated where the variable is defined. Reusing the variable returns that stored result; it does not automatically reevaluate the expression under a later context modification.
Problematic pattern
CurrentSales was already evaluated before CALCULATE attempted to remove the Product filters.
Correct pattern
Useful variable pattern
16. Understanding total rows
Totals frequently confuse report developers because the total context differs from the individual row contexts.
Example: profitable product count
On each category row, VALUES returns products in that category. On the total row, VALUES returns products across all visible categories. The measure is then evaluated over that larger set.
Non-additive measures
Measures that are often non-additive include:
- Distinct customer count
- Percentages
- Ratios
- Averages
- Minimum and maximum
- Balances
- Rankings
17. Additive total versus recalculated total
Recalculated total
The total is calculated as total sales divided by total distinct customers. It should not normally be the sum of regional averages.
Explicit sum of visible row results
This measure explicitly iterates visible categories and adds the measure result obtained for each category.
18. Debugging evaluation context
Complex DAX should be tested by exposing individual context components instead of attempting to debug the complete measure at once.
Show the selected category
Count visible products
List visible categories
Check whether one value is in scope
Recommended debugging tools
- Temporary diagnostic measures
- Simple table or matrix visuals
- DAX query view
- Performance Analyzer
- Controlled filters with known expected results
19. DAX query view
DAX query view allows developers to evaluate model measures and table expressions outside a report visual.
Example query
This query groups results by product category and evaluates the two measures in each category's filter context.
Useful testing process
20. Worked example: discounted profitability
Business requirement
Calculate net revenue and gross profit at sales-line level after applying each line's recorded discount percentage.
Base cost measure
Net revenue measure
Gross profit
Gross margin percentage
Profitable product count
Why evaluation context matters
- SUMX evaluates revenue and cost for every visible transaction line.
- The visible transaction rows are determined by filter context.
- VALUES returns products visible in the current report context.
- Gross Profit is evaluated separately for each product inside FILTER.
- The total profitable-product count is recalculated across all products visible in the total context.
21. Common DAX evaluation mistakes
| Mistake | Consequence | Correction |
|---|---|---|
| Treating row context as filter context | Aggregations return unexpected totals | Apply context transition where required |
| Using SUM for a row-level expression | Required multiplication or conditional logic is absent | Use an iterator such as SUMX |
| Using FILTER for every simple condition | Unnecessary table iteration | Prefer Boolean filter arguments where sufficient |
| Expecting CALCULATE filters to always intersect | Existing same-column filters are replaced | Use KEEPFILTERS when intersection is intended |
| Storing a measure result in a variable too early | Later filter modifications do not reevaluate it | Evaluate the measure inside the intended context |
| Assuming totals add visible rows | Non-additive totals appear incorrect | Analyse the total row's filter context |
| Removing filters from an entire table unnecessarily | More filters are removed than intended | Target the specific column where appropriate |
| Ignoring model relationships | Formula behaviour appears inconsistent | Trace the complete filter-propagation path |
22. Hands-on laboratory
Lab: Analyse DAX evaluation contexts
Participants receive a sales semantic model containing product, customer, date, region and transaction tables.
Task 1: Observe filter context
- Create Total Sales.
- Display it by product category and year.
- Add region and channel slicers.
- Document the context of one matrix cell.
Task 2: Use row context
- Create line-level revenue with SUMX.
- Create line-level cost with SUMX.
- Compare the results with stored source totals.
Task 3: Modify context
- Create sales for one product color.
- Create sales across all product colors.
- Calculate each color's percentage of total sales.
- Compare replacement and KEEPFILTERS behaviour.
Task 4: Test context transition
- Iterate visible products.
- Evaluate Total Sales for each product.
- Explain how the current product becomes a filter.
Task 5: Investigate totals
- Create a distinct customer count.
- Display it by region.
- Compare the grand total with the sum of regional values.
- Explain why they may differ.
Task 6: Debug the measure
- Create selected-value diagnostic measures.
- Count visible products and customers.
- Run a summary query in DAX query view.
- Validate results against expected totals.
Expected deliverable
A Power BI report containing documented examples of filter context, row context, iterators, context transition, CALCULATE filter modification, KEEPFILTERS and non-additive totals.
23. Knowledge check
24. Advanced DAX evaluation checklist
- The expected filter context is documented
- Relationship filter paths have been verified
- Row context is used only where required
- Context transition is applied deliberately
- Iterators operate over the smallest appropriate table
- CALCULATE filters add, replace or remove filters as intended
- KEEPFILTERS is used where intersection is required
- FILTER is reserved for genuinely complex conditions
- Variables are defined in the correct evaluation context
- Total and subtotal behaviour has been tested
- Diagnostic measures confirm visible values and rows
- Results agree with authoritative source totals