Concept Breakdown
The REMOVEFILTERS function, typically used as a modifier within CALCULATE, allows you to clear filters from tables or columns. When REMOVEFILTERS() is used without any arguments, it removes all filters from the entire table. When specific columns are passed as arguments (e.g., REMOVEFILTERS(Table[Column])), it clears filters only from those specified columns. This is crucial for creating measures that display values independent of the current visual's filter context, such as 'total sales' or 'average for all categories' for comparison purposes.
What You Will Learn
Understand when to use REMOVEFILTERS, CALCULATE in a Power BI model.
Practice the concept inside a real PBIX report rather than only reading syntax.
Complete a beginner-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 'Tallest Buildings - Calculate Remove Filters.pbix' file. Open it in Power BI Desktop.
Observe the gauge and table visuals. Note how the 'Max height' measure on the gauge, and the 'Avg height' in the table, change when you select different countries in the table.
Create a new measure called `Max height no filters`. This measure should calculate the maximum height of buildings but remove all active filters from the data model.
Assign the `Max height no filters` measure to the 'Maximum value' bucket of the gauge visual, replacing the existing 'Max height' measure.
Create a new measure called `Avg height all countries`. This measure should calculate the average height of buildings but remove any filters applied specifically by the `Country[Country]` field.
Add the `Avg height all countries` measure to the table visual and verify that each row now displays the same global average height.
Create a new measure called `Avg height compared to all countries`. This measure should calculate the difference between the current row's 'Avg height' and the 'Avg height all countries'.
Add the `Avg height compared to all countries` measure to the table visual. Apply conditional formatting, such as data bars, to this measure for better readability.
Save and close the report.
Starter DAX
Avg height = AVERAGE('Tallest Buildings'[Height (m)])
Max height = MAX('Tallest Buildings'[Height (m)])Expected Outcome
The gauge's maximum value will remain constant regardless of which country is selected in the table. The `Avg height all countries` column in the table will display the overall average height for all buildings across all countries on every row. The `Avg height compared to all countries` column will show how much taller or shorter each country's average building height is compared to the global average, with conditional formatting highlighting the differences.