ALLEXCEPT Function (DAX)

How does the ALLEXCEPT function (DAX) work?

The ALLEXPECT function (DAX) removes all context filters in the table except filters that have been applied to the specified columns.

ALLEXCEPT Formula Syntax

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

How do you use the ALLEXCEPT function?

This function cannot be used alone; instead, it serves as an intermediary function to change the results on which another calculation is performed. 

This is a convenient way for you to remove the filters on many, but not all, columns in a table. 

Related Blog Posts

Related Support Forum Posts

Using ALL and ALLEXCEPT
Date Table with ALLEXCEPT
Allexcept() Function Tutorial

Considerations when using the ALLEXCEPT function

Its parameters are the table in which all context filters are removed and the column in which the context filters are retained. The column can be a part of the expanded table. 

The first argument to ALLEXCEPT is that it needs to serve as a reference to a base table, while all the subsequent arguments must serve as references to base columns. The ALLEXCEPT function cannot be used in both table and column expressions. 

The ALLEXCEPT function does not materialize the resulting table when called directly in a filter argument of CALCULATE or CALCULATETABLE. 

Related Video Tutorials

Formula examples using the ALLEXCEPT function

=CALCULATE(
SUM(ResellerSales_USD[SalesAmount_USD]),
ALLEXCEPT(DateTime, DateTime[CalendarYear])
)

ALLEXCEPT ( Customer, Customer[City] )

ALLEXCEPT ( Sales, ‘Date’, Customer[City] )

Related DAX Functions

FILTER Function (DAX)

Related Course Modules

DOWNLOAD


Download our comprehensive DAX Formula Reference Guide as perfect companion as you learn how to use DAX formulas within Power BI.


Download DAX Formula Reference Guide