How ALL, ALLEXCEPT and REMOVEFILTERS help you control filter context
Writing measures in Power BI is an easy way to do quick calculations in your view to aid your analysis, however, you can easily run into filter context problems when using slicers, filters or even just dimensions in your view (any dimension you drag into your chart or table is automatically acting like a filter on your measures).
We can get around these problems with three functions. ALL, ALLEXCEPT, and REMOVEFILTERS.
These functions can help you to control which filters are affecting your calculations. Let’s go through them using a superstore dataset for the examples.
A Quick Note on Calculate
These examples use the CALCULATE function.
In DAX, CALCULATE is what activates filter changes. It’s like saying:
“Take this measure, but apply these new filter rules instead.”
Functions like ALL, ALLEXCEPT, and REMOVEFILTERS only work inside other functions where filter context matters (e.g. CALCULATE, RANKX, SUMX, etc..) . Essentially Anywhere you're shaping what rows get considered , these functions can help.
ALL Function - Wipe All Filters
The ALL function allows you to ignore all filters in a selected table and calculate a grand total.
E.g. Total Sales (Ignore Filters) = CALCULATE([Total Sales], ALL(Sales))
This will calculate the total sales, even if regional filter (for example) was selected.
ALLEXCEPT Function - Only keep one filter, ignore the rest
The ALLEXCEPT function allows you to remove all filters except for one (or more) specific fields. It's a useful way to keep one filter in place while ignoring others that might be affecting your calculation. Let’s say you want to see total sales by category, even if a Region, Product, or Year slicer is applied:
E.g. Total Sales by Category = CALCULATE([Total Sales], ALLEXCEPT(Orders, Orders[Category]))
This will still break sales down by Category, but it will ignore all other filters like country or region. You’ll always get the full category totals, even if other slicers are used in the report.
You can use this when you want to compare values within a category, or show the full total for that category regardless of what's selected elsewhere.
REMOVEFILTERS Function
The REMOVEFILTERS will do the opposite of the ALLEXCEPT and will allow you to ignore just a single filter.
E.g. Sales (Ignore Region) = CALCULATE([Total Sales], REMOVEFILTERS(Sales[Region]))
This will remove the region filter but all of the other filters will still be in effect.
Summary
ALL- Ignores all filters of the selected tableALLEXCEPT- States what filters NOT to ignore (Don't remove '___' filter)REMOVEFILTERS- States what filters to ignore (remove '___' filter)
These functions are similar to FIXED LODs in Tableau and allow you to take control of how filters affect your measures. Whether you want to compare values across the whole dataset, calculate within a category, or ignore just one specific filter, ALL, ALLEXCEPT, and REMOVEFILTERS give you the flexibility to do exactly that.
Understanding how filter context works, and how to override it, is a really powerful skill in DAX. Using these functions will help you to build cleaner, smarter visuals that behave exactly how you want them to.