Introduction
Filter modifiers within PowerBi are incredibly powerful ways of making calculations dynamic and context-aware. In this blog, we will explore three key filter-modifying functions—REMOVEFILTERS(), ALLSELECTED(), and ALLEXCEPT().
Filter Modifiers in Action
The most effective way to demonstrate each function is through a visual demonstration. For this blog I have used the Tableau superstore dataset and set up the following DAX calculations.
REMOVEFILTERS()
Remove filters in PowerBI clears any filters applied to a specified column or table, effectively resetting the filter context for a calculation. In the example below, this results in each row reflecting the total sales across all three categories, ignoring the fact that only two categories are selected.
The DAX calculation we made only removes Category from context. Therefore, when we select a different type of filter (Year), the values will update.
ALLSELECTED()
This function removes filters from all columns, except those that are currently selected or applied in the visual context, such as slicers or visual filters. In the example below it returns the total sales, only for the two categories selected.
The value updates to match whichever category is selected. For example, if we remove technology from our filter this will no longer be considered in the calculation.
The ALLSELECTED calculation also updates based on any filters that aren't specified. If a year is selected within our example above, then the calculation will update to sum the total sales across selected sub-categories, within selected years. (The total sales of office supplies and tech combined in 2024).
ALLEXCEPT()
This function removes all filters from a table, except for the specified columns. Selected columns retain their filter context while ignoring others. In the example below, each row is fixed based on category and, therefore, both the Total Sales column and All Except column appear to be the same.
The distinction occurs when a filter not specified in the DAX is applied. All Selected will ignore all filters, except the one specified. So, therefore, when the Year filter is applied, it is ignored.
