ALLEXCEPT DAX Function

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:

tablename -  The name of an existing table.

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

Step 3: Create Measure and write DAX formula for ALLEXCEPT function.

ALLEXCEPT_SALES =

CALCULATE (

SUM ( Orders[Sales] ),

 ALLEXCEPT ( Orders, Orders[Product Category] )

Step 4: Now Drag ALLEXCEPT_SALES measures into table.

Step 5: Now put filter on Product Category & see the measure ALLEXCEPT_SALES result, it is returning Total Sales sum of Furniture.

Step 6: Now put filters on both slicers and see the result.

According to definition, it is avoiding filter for Product sub category slicer and only returning specified filter values like Product Category.

Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome