Home » DAX » DAX – ALLEXCEPT function

DAX – ALLEXCEPT 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. It comes under Filter functions Dax category.


ALLEXCEPT (<table>, <column>, [<column>] …)


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

Visualization – Slicers & Table

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

    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, 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

ALLEXCEPT function in Power Bi

Step-6: Now, apply filters to both slicers and observe the resulting changes.

Power Bi ALLEXCEPT DAX function example

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:







Leave a Reply