ALLEXCEPT is a DAX function and it removes all context filters in the table except filters that have been applied to the specified columns. It comes under Filter functions Dax category.
Syntax:
ALLEXCEPT (<table>, <column>, [<column>] …)
Description:
| S no. | Parameter | Description |
| 1 | table name | The name of an existing table. |
| 2 | Column | The column for which context filters must be preserved and it is repeatable. |
Let’s start with an example:
Step-1: Download the Sample data : SuperStoreUS-2015.xlxs
Step-2: Drag the table and slicers from the visualization pane.
Slicers: Drag ‘Product Category’ into the first slicer and ‘Product Sub Category’ into the second slicer.
Table: Drag three fields into the table—’Product Category,’ ‘Product Sub Category,’ and ‘Sales’ from the Orders Dataset.
Visualization – Slicers & Table
Step-3: Create a Measure and write DAX formula for ALLEXCEPT function.
ALLEXCEPT_SALES = CALCULATE ( SUM ( Orders[Sales] ), ALLEXCEPT ( Orders, Orders[Product Category] ) )
Power Bi ALLEXCEPT DAX function
Step-4: Now Drag ALLEXCEPT_SALES measures into table.
Tables & Slicers in Power Bi
Step-5: Now, apply a filter to the Product Category and observe the outcome of the measure ‘ALLEXCEPT_SALES.’
You will notice that it returns the summation of Total Sales specifically for the Furniture category.
ALLEXCEPT function in Power Bi
Step-6: Now, apply filters to both slicers and observe the resulting changes.
Power BI ALLEXCEPT DAX function example
As per the definition, it is avoiding filter for Product sub category slicer and only returning specified filter values like Product Category.
Refer Other DAX functions: DAX functions
Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome or you can leave us message on our contact form , we will revert to you asap.
Recommended Power BI Post: