Concept Breakdown
KEEPFILTERS is a DAX function that modifies the filter behavior of CALCULATE. When used within a CALCULATE function's filter arguments, KEEPFILTERS ensures that existing filters in the filter context are preserved and combined with the new filters specified within CALCULATE, rather than being overwritten. This allows for additive filtering, enabling scenarios where you want to add a filter while respecting what is already filtered, or selectively include/exclude items based on specific conditions without fully overriding the visual's context.
What You Will Learn
Understand when to use KEEPFILTERS, CALCULATE in a Power BI model.
Practice the concept inside a real PBIX report rather than only reading syntax.
Complete a intermediate-level task and verify the result with a hidden answer check.
Practice in Power BI
The starter PBIX link has not been attached yet. The student workflow is ready for the admin to connect the file.
Download and unzip the provided 'Music Tours - Calculate Keep Filters.pbix' file.
Open the PBIX file in Power BI Desktop.
Navigate to Page 1 of the report. Observe the existing bar chart displaying the average peak chart position for albums by different artists.
Create a new measure called `Average Peak Position`. Define it as `AVERAGEX('Albums', 'Albums'[Peak Chart Position])`.
Create a new measure named `Average Peak Position - Selected Artists`. This measure should use `CALCULATE` and `KEEPFILTERS` to show the average peak chart position for any three artists of your choice. Refer to the `dax_code` section for an example.
Replace the current measure in the existing bar chart on Page 1 with your newly created `Average Peak Position - Selected Artists` measure.
Create another measure named `Average Peak Position - Excluded Artists`. This measure should use `CALCULATE` and `KEEPFILTERS` to show the average peak chart position, explicitly excluding any three artists of your choice. Refer to the `dax_code` section for an example.
Create a new bar chart on Page 1. Add the 'Artist' field to the Axis and your `Average Peak Position - Excluded Artists` measure to the Values.
Navigate to Page 2 of the report. You will find a line chart showing the percentage of cancelled shows per year.
Assume there's an existing base measure `[Percentage of Cancelled Shows]`.
Create three new measures to represent the percentage of cancelled shows for specific periods: `Cancelled Shows Pre-COVID` (up to and including 2020), `Cancelled Shows During COVID` (2020 to 2022 inclusive), and `Cancelled Shows Post-COVID` (2022 or later). Use `CALCULATE` with appropriate date filters for each. Refer to the `dax_code` section for examples.
Add these three new measures to the line chart on Page 2, replacing the original `[Percentage of Cancelled Shows]` measure. This will create three distinct lines.
Apply formatting to the lines (e.g., different colors, styles) for the three series to visually distinguish the Pre-COVID, During COVID, and Post-COVID periods.
Save and close the report.
Starter DAX
Average Peak Position = AVERAGEX('Albums', 'Albums'[Peak Chart Position])Expected Outcome
On Page 1, two bar charts will be visible. The first will show the average peak chart position only for your chosen 'selected artists'. The second will display the average peak chart position for all artists except your chosen 'excluded artists'. On Page 2, the line chart will now contain three distinct lines, each representing the percentage of cancelled shows for the 'Pre-COVID', 'During COVID', and 'Post-COVID' periods, with customized formatting to differentiate them clearly.