ALLEXCEPT is a DAX function and it removes all context filters in the table except filters that have been applied to the specified columns. Its 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 Sample data : SuperStoreUS-2015.xlxs
Step 2: Drag Table & Slicers from visualization Pane.
- Slicers: Drag Product Category in first slicer & Product sub category in second slicer
- Table: Drag three fields in table, Product Category, Product Sub Category & Sales from Orders Dataset
Visualization – Slicers & Table
Step 3: Create 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 put filter on Product Category & see the measure ALLEXCEPT_SALES result, it is returning Total Sales sum of Furniture.
ALLEXCEPT function in Power Bi
Step 6: Now put filters on both slicers and see the result.
Power Bi ALLEXCEPT DAX function example
According to 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: