DAX KEEPFILTERS and REMOVEFILTERS: why a chart can change its totals
Practical examples of how CALCULATE replaces, intersects or removes parts of the active filter context.
Identify the existing filter context
Year, region and product slicers cause the same Power BI measure to run with different filters. CALCULATE evaluates an expression in a modified filter context. By default, a new filter argument can replace existing filters on the same column. Consequently, a blue-products sales measure may still return a value when the viewer had selected red products.
KEEPFILTERS intersects selections
KEEPFILTERS changes how CALCULATE combines filters on the same columns: the intersection is used instead of unconditional replacement. If the viewer selects red and the measure explicitly adds blue with KEEPFILTERS, a blank result may be the correct answer. This matters when the business meaning of a user selection must be preserved.
BlueSales =
CALCULATE(
SUM(Sales[Amount]),
KEEPFILTERS(Product[Color] = "Blue")
)REMOVEFILTERS creates a deliberate denominator
For percentage-of-total calculations, some filters must be removed intentionally. REMOVEFILTERS clears filters for specified columns or tables; removing every filter is often unnecessary. A denominator preserving a region selection but removing product filters answers a different question from a company-wide denominator. Stakeholders should agree on the denominator before comparing percentages.
Validate through controlled examples
Test the measure with no filter, one selected color, and multiple slicers together. Before calling differences between row totals and grand totals a bug, inspect filter contexts. Tools such as Performance Analyzer can reveal query behavior, but execution time and correctness against business rules are separate concerns.
Comments
0 real commentsNo real reader comments yet.
✎ Write a comment