Introduction
A sales measure shows the expected total in a Power BI card, but changes inside a matrix. A percentage becomes 100% on every row. An iterator returns twice the expected amount.
These results often become easier to explain once you identify the context in which the formula runs.
In DAX, or Data Analysis Expressions, filter context determines which data contributes to a calculation. CALCULATE changes that context, ALL can remove specified filters, and context transition applies values from a current row as filters. This cheat sheet uses one small model to show the differences.
Start With Row Context and Filter Context
| Concept | What it means | Typical source |
| Filter context | The filters in effect when an expression is evaluated | Slicers, report filters, matrix groupings and DAX filter arguments |
| Row context | The current row available to an expression | Calculated columns and iterators such as SUMX |
| Context transition | Applying current row-context values as filters | CALCULATE evaluated within row context; a measure reference also triggers it automatically |
A matrix row creates filter context for its measures. Its appearance as a row on screen does not create DAX row context.
If you know SQL filtering, WHERE narrows the rows considered by a query. With a DAX measure, also identify the filters supplied by each visual cell and the model's relationships.
The sample model
Use an Import model with these two tables. All amounts are illustrative.
Product
| ProductID | Category |
| P1 | Accessories |
| P2 | Computers |
Sales
| OrderDate | ProductID | Amount |
| 2026-01-05 | P1 | 100 |
| 2026-01-06 | P2 | 300 |
| 2026-02-05 | P1 | 200 |
| 2026-02-06 | P2 | 400 |
Add a Date table containing every date from 1 January to 28 February 2026, with a Month label such as 2026-01. Create active, one-to-many relationships from Product[ProductID] to Sales[ProductID] and from 'Date'[Date] to Sales[OrderDate]. Use single-direction filtering from each dimension into Sales.
Create each formula below as a separate measure. Unless stated otherwise, select January using 'Date'[Month], with no other filters.
Total Sales = SUM ( Sales[Amount] )
This returns 400 in a card. With Product[Category] on matrix rows, it returns 100 for Accessories and 300 for Computers.
CALCULATE: Change the Filters Behind a Measure
Microsoft defines CALCULATE as evaluating an expression in a modified filter context. A simple category filter illustrates the behaviour:
Accessories Sales =
CALCULATE (
[Total Sales],
Product[Category] = "Accessories"
)
The January result is 100. The category condition restricts Product, and the relationship passes that filter to Sales. The date selection remains active.
Now select Computers in a Product[Category] slicer. The result is still 100: CALCULATE replaces the existing filter on that same column with Accessories.
Use KEEPFILTERS when both the existing selection and the new condition should apply:
Accessories Within Selection =
CALCULATE (
[Total Sales],
KEEPFILTERS ( Product[Category] = "Accessories" )
)
With Computers selected, this returns BLANK, because the two category conditions have no overlap.
| Existing category selection | Accessories Sales | Accessories Within Selection |
| None | 100 | 100 |
| Accessories | 100 | 100 |
| Computers | 100 | BLANK |
Replacement applies to the column named in the filter argument. A filter on another column, such as ProductID, can still restrict the result.
ALL: Choose What Your Total Includes
Clear the category slicer. To calculate each category's share of January sales, remove the category filter from the denominator:
Category Share =
DIVIDE (
[Total Sales],
CALCULATE (
[Total Sales],
ALL ( Product[Category] )
)
)
Format this measure as a percentage. In the category matrix, Accessories returns 100 ÷ 400 = 25%, and Computers returns 300 ÷ 400 = 75%.
The numerator retains the current category. The denominator removes that category filter while retaining January.
Choose the removal scope deliberately:
| Modifier inside CALCULATE | Effect in this model |
| ALL ( Product[Category] ) | Clears the category filter; a ProductID filter can remain |
| ALL ( Product ) | Clears Product filters, including Category and ProductID; Date filters remain |
| ALL () | Clears ordinary query filters across the model, including Date |
For example, filter only ProductID to P1 while keeping January selected. Clearing Category alone leaves the denominator at 100. Clearing Product makes it 400.
If the purpose is solely to clear filters, Microsoft recommends REMOVEFILTERS where supported. Here, replacing ALL ( Product[Category] ) with REMOVEFILTERS ( Product[Category] ) produces the same denominator. ALL with a table or column argument can also return a table; REMOVEFILTERS only clears filters.
Multiple filters on the same table can also interact through DAX's auto-exist optimisation, which considers existing combinations of values. Check those slicer combinations before assuming a denominator represents every possible category.
Context Transition: Turn the Current Row Into a Filter
Reset Product filters and keep January selected. Consider this deliberately incorrect attempt to sum sales by product:
Repeated Total =
SUMX (
VALUES ( Product[ProductID] ),
SUM ( Sales[Amount] )
)
VALUES supplies the two product IDs, and SUMX evaluates its expression once for each. That creates row context, but the plain SUM does not use it to filter Sales.
Each iteration therefore returns January's 400. The card displays 800.
Introduce context transition:
Sales Through Transition =
SUMX (
VALUES ( Product[ProductID] ),
CALCULATE ( SUM ( Sales[Amount] ) )
)
CALCULATE turns the current product ID into a filter. The iterations now return 100 and 300, giving 400.
Referencing the existing measure also works:
Sales Through Measure =
SUMX (
VALUES ( Product[ProductID] ),
[Total Sales]
)
A model measure evaluated within row context triggers context transition automatically. This explains why replacing a measure reference with its underlying SUM expression can change an iterator's result.
These iterator measures demonstrate the mechanism. For a simple sales total, use [Total Sales] directly; iteration becomes useful when the business calculation needs work performed for each product.
Check These Causes of Unexpected DAX Results
Start by displaying the base measure and checking one filter at a time.
| Symptom | What to inspect |
| Every percentage is 100% | Does the denominator still have the numerator's filters? |
| A category slicer seems ineffective | Does CALCULATE replace that column's filter, or ALL remove it? |
| An iterator repeats the total | Does the aggregation need context transition? |
| A dimension slicer changes nothing | Is its relationship active, with the intended filter direction? |
| The total differs from summed rows | The measure is evaluated again in the total's context; it is not necessarily a sum of displayed results |
When discussing a measure in ConnectX, PangaeaX's community for data professionals, share the formula, a small sample, relationships, slicer selections and expected output. This gives others enough context to examine the behaviour.
Put the Patterns Into Practice
Try these exercises with the sample model:
- Switch to February, with no Product filters. Total Sales should be 600; category shares should be approximately 33.33% and 66.67%.
- Select February and Computers. Accessories Sales should return 200, while Accessories Within Selection should be blank.
- Clear all Date and Product filters. Total Sales should return 1,000. Repeated Total should return 2,000, while both transition examples return 1,000.
CompeteX is PangaeaX's data competition platform, with business intelligence and analytics challenges. When choosing data analytics challenges, check the required tools and task format, then look for opportunities to practise metric calculation and interpretation.
Explain which filters each measure keeps, replaces or removes alongside your answer. Predicting the result, changing one selection and explaining the difference is a useful way to turn these patterns into dependable DAX skills.