KEEPFILTERS and filter intersection
A filter argument in CALCULATE replaces the filter on its column. CALCULATE([Sales Amount], Product[Color] <> "Blue") throws away the color of the matrix row: the Blue row then shows the sales of every other color.
KEEPFILTERS changes replace into intersect. The new filter is added on top of the one already there, so a Blue row intersected with "not Blue" is empty, and every other row keeps its own color.
Your task
Write a measure named Sales excl. Blue that returns Sales Amount without blue products, and leaves each row's own color filter in place.
About this exercise
You practise when a filter should not replace with CALCULATE and KEEPFILTERS. The exercise runs on a small bike-shop model with a Sales fact table and Product, Customer and Date dimensions, the same star schema in every lesson.
Your measure, Sales excl. Blue, fills a Power BI-style matrix grouped by Product[Category] and Product[Color]. As in a real report, each cell evaluates the measure in its own filter context, and the subtotals and the grand total are evaluated again in theirs rather than added up. When you check your answer, every cell is compared with the expected value, wrong cells are marked, and the hint is about the mistake you made.
Measures that already exist in the model: Sales Amount and Total Cost. Reference them in square brackets, for example [Sales Amount].
Where you start
Sales excl. Blue =The result to build
| Category › Color | Sales excl. Blue |
|---|---|
| Accessories | 24,366.00 |
| Black | 17,350.90 |
| Blue | |
| Silver | 7,015.10 |
| Bikes | 146,880.00 |
| Black | 78,540.00 |
| Blue | |
| Red | 68,340.00 |
| Clothing | 20,217.55 |
| Black | 9,972.20 |
| Red | 10,245.35 |
| Total | 191,463.55 |
Last updated 10 November 2026
Opening the exercise…