Returns all the rows in a table except for those rows that are affected by the specified column filters.
The name of an existing table.
A column or a tablet whose filtering is to be retained when ALLEXCEPT is used as a CALCULATE modifier. The column/table must be part of the expanded table specified in the first parameter.
When used as a modifier in CALCULATE or CALCULATETABLE, ALLEXCEPT removes the filters from the expanded table specified in the first argument, keeping only the filters in the columns specified in the following arguments.
When used as a table function, ALLEXCEPT materializes all the unique combinations of the columns in the table specified in the first argument that are not listed in the following arguments. In this case, the result only has the columns of the table and ignores the expanded table.
For example, having a table T with four columns (a, b, c, d), the two following table expressions are equivalent:
FILTER ( ALLEXCEPT ( T, T[a], T[b] ), -- The result as a table expression has only T[c] and T[d] <expr> ) -- The result of the following expression is identical to the previous one FILTER ( ALL ( T[c], T[d] ), -- The result as a table expression has only T[c] and T[d] <expr> )
However, ALLEXCEPT is commonly used as a CALCULATE modifier and very rarely needed as a table function.
Remove filters from all the columns of the Customer table but City.
CALCULATE ( <exp>, ALLEXCEPT ( Customer, Customer[City] ) )
Remove filters from all the columns of the expanded table Sales but City.
CALCULATE ( <exp>, ALLEXCEPT ( Sales, Customer[City] ) )
Remove filters from all the columns of the expanded table Sales but Date table and City column.
CALCULATE ( <exp>, ALLEXCEPT ( Sales, 'Date', Customer[City] ) )
Learn more about ALLEXCEPT in the following articles:
Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT
This article provides a complete explanation of the behavior of the ALLxxx functions in DAX. When used as filters in CALCULATE, ALLxxx functions might display unexpected behaviors. » Read more
Using ALLEXCEPT versus ALL and VALUES
This article describes the semantic difference between ALLEXCEPT and the joint use of ALL and VALUES, showing practical examples of the different results in Power BI and SSAS 2016. » Read more
Expanded tables in DAX
Expanded tables are the core of DAX; understanding how they work is of paramount importance. This article provides a theoretical foundation of what expanded tables are, along with fundamental concepts useful when reading DAX code. » Read more
Understanding Circular Dependencies in Tabular and PowerPivot
When you design a data model for Tabular you should pay attention to a though topic, which is that of circular dependencies in formulas. It is very important to learn how to handle circular dependencies now because in SQL 2012… » Read more
Other related functions are:
Contributors: Alberto Ferrari, Marco Russo
MSDN documentation: https://docs.microsoft.com/en-us/dax/allexcept-function-dax