Power BI / Intermediate: context, quality and access / Lesson 7
CALCULATE and filter modification
Predict how a measure changes when a calculation replaces, intersects or removes a documented filter.
On this page
Lesson goals
- Explain CALCULATE as evaluation under modified filter context.
- Distinguish replacement from KEEPFILTERS intersection.
- Define a denominator’s retained and removed dimensions.
Name the starting context
A measure does not have one permanent value independent of the report. Begin with the filters already present, then describe what CALCULATE changes before evaluating its expression. The course assumes the active DimProduct-to-FactSales relationship and the70 total fixture. A visual row for product B initially restricts revenue to20. Without this starting state, an explanation such as sales for A is incomplete: it does not say whether A replaces the existing product choice or narrows it. Write the before, modification and expected included rows as three separate parts of the calculation review.
Compare replace and intersect
A simple CALCULATE filter on a column normally replaces an existing filter on that same column. KEEPFILTERS changes the combination to intersection. In the first snippet, a B row can show50 for A Revenue because the ProductKey filter becomes A, while A Within Selection has no matching product under the intersection. Filters on other columns remain relevant unless explicitly modified. These definitions are model fragments with reasoned expectations, not executed DAX. Test a conflicting selection as well as a matching one; a default unfiltered total cannot reveal the difference between these two contracts.
A Revenue = CALCULATE([Revenue], DimProduct[ProductKey] = "A")
A Within Selection = CALCULATE([Revenue], KEEPFILTERS(DimProduct[ProductKey] = "A"))Assume Revenue is the earlier SUMX measure and ProductKey is the visual filter column. Under B, the intended results are50 and BLANK respectively. No DAX engine ran.
Use a set oracle
The second example models the two product-set decisions only. Replacing {B} with {A} leaves A; intersecting them leaves no product. It deliberately prints sets as sorted lists and does not claim to reproduce DAX BLANK or relationship evaluation. Separating selection logic from arithmetic makes an unexpected result easier to explain. After the selected product set is understood, apply the known line amounts and compare the intended result. Also check filters on other product attributes, because replacing one column does not imply clearing every possible filter on the dimension.
existing = {"B"}
requested = {"A"}
print(sorted(requested))
print(sorted(existing & requested))['A']
[]The lists model replacement and intersection only. An empty Python list is not asserted to be the DAX BLANK value.
Review modifiers in context
Use a matrix of no selection, A, B, multiple products and a retained date filter. Explain totals under the same rules rather than forcing them to resemble visible row addition. Boolean filter arguments have language restrictions, so consult the function reference before placing a complex cross-table condition into one argument. This course executes the set model only. Actual DAX parsing, blank behavior and filter propagation remain untested. A clear handoff names the initial context, exact modified columns, retained filters and expected rows for every important case, making the formula’s meaning reviewable before report design hides it.
Common errors and fixes
A fixed-product measure ignores the selected product unexpectedly
- Why it happens
- Same-column replacement was mistaken for intersection.
- How to fix it
- Choose replacement or KEEPFILTERS according to the metric contract.
- Prevent it next time
- Include a conflicting-product fixture in review.
A share denominator loses its date scope
- Why it happens
- A broader filter removal was used than the question required.
- How to fix it
- Remove only the intended dimension filters and retain documented context.
- Prevent it next time
- Write denominator scope before selecting a modifier.
An empty intersection is treated as a broken model
- Why it happens
- The conflicting selections were not understood.
- How to fix it
- Trace the included key set before changing relationships or blanks.
- Prevent it next time
- Separate set logic from amount arithmetic in expected results.
Check your understanding
1. Under an existing B filter, why can A Revenue show50 while A Within Selection is blank?
Show worked solution
The first replaces the same ProductKey column filter with A; the second intersects B with A and finds no matching product. Other relevant filters remain. The difference is intentional calculation semantics, so decide which question the measure should answer before calling either result wrong or replacing the blank with zero.
2. A share must compare products within the selected month. What should the denominator retain?
Show worked solution
Retain the selected date/month context while removing only the product scope defined by the business question. Document whether all products or only a selected category population belongs in the denominator. Verify A’s50 contribution and the corresponding population total using a small case with at least two dates and products.
Key takeaways
- CALCULATE changes filter context; replacement and intersection answer different questions.
- A denominator requires explicit retained and removed dimensions.