Home » DAX » DAX – ALLEXCEPT function

DAX – ALLEXCEPT function

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:

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

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] )
)
ALLEXCEPT function

Power Bi ALLEXCEPT DAX function

Step 4: Now Drag ALLEXCEPT_SALES measures into table.

Tables & Slicers in Power Bi

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

ALLEXCEPT function in Power Bi

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

Power Bi ALLEXCEPT DAX function example

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:

UNION

DATATABLE

ALLSELECTED

ALL

ADDCOLUMNS




Leave a Reply