Keep slicer selections with KEEPFILTERS
Calculate a value for one category without overriding what the user selected in a slicer.
Bikes Sales (respects slicers) =
CALCULATE(
[Total Sales],
KEEPFILTERS(Product[Category] = "Bikes")
)How it works
A plain CALCULATE(..., Product[Category] = "Bikes") replaces any existing filter on Category, so a slicer that has Accessories selected is ignored and bike sales appear anyway.
KEEPFILTERS makes the new filter intersect with the existing one instead. With Accessories selected the result is blank, because no row is both a bike and an accessory.
What your model needs
- A Product table with a Category column, related to Sales. - Total Sales = SUM(Sales[Amount]) (called [Total Sales]).
Small example
Input data
Slicer: Category = Accessories
Bike sales = 4,000Expected result
Plain CALCULATE = 4,000
With KEEPFILTERS = (blank)Common mistakes and limitations
- Most measures should not need KEEPFILTERS. Use it only when the measure must never show data outside the user's selection.