ALLEXCEPT DAX Function (Filter)

Returns all the rows in a table except for those rows that are affected by the specified column filters.

Syntax

ALLEXCEPT ( <TableName>, <ColumnName> [, <ColumnName> [, … ] ] )
Parameter Attributes Description
TableName

The name of an existing table.

ColumnName Repeatable

A column or a table 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.

Return values

Table An entire table or a table with one or more columns.

Remarks

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.

» 5 related articles
» 3 related functions

Examples

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

ALLEXCEPT used as a table function returns a table removing columns and duplicated rows.

--  Returns all the 'Product' columns 
EVALUATE 
ALL ( 'Product' )
    
--  Returns all the 'Product' columns but ProductKey and Product Code
EVALUATE 
ALLEXCEPT ( 'Product', 'Product'[ProductKey], 'Product'[Product Code] )
--  Reducing the number of columns returned 
--  also reducse the number of rows
EVALUATE
ALLEXCEPT (
    'Product',
    'Product'[ProductKey],
    'Product'[Product Code],
    'Product'[Product Name],
    'Product'[Manufacturer],
    'Product'[Brand]
)
-- The following query returns all the products 
-- with Contoso brand, regardless of the color
EVALUATE
CALCULATETABLE (
    CALCULATETABLE (
        'Product',
        ALLEXCEPT ( 'Product', 'Product'[Brand] )
    ),
    'Product'[Brand] = "Contoso",
    'Product'[Color] = "Red"
)
--  In this example, ALLEXCEPT ignores Sales expanded table filters
--  except the cross-filters coming from Date and the column Product[Color]
DEFINE 
    MEASURE Sales[# Sales] = COUNTROWS ( Sales ) 
EVALUATE
CALCULATETABLE (
    {
         ( 1, "# Sales (CY 2009 - Red)", [# Sales] ),
         ( 2, "# Sales (CY 2009)",       CALCULATE ( [# Sales], ALLEXCEPT ( Sales, 'Date' ) ) ),
         ( 3, "# Sales (Red)",           CALCULATE ( [# Sales], ALLEXCEPT ( Sales, 'Product'[Color] ) ) ),
         ( 4, "# Sales",                 CALCULATE ( [# Sales], REMOVEFILTERS ( ) ) )
    },
    'Product'[Color] = "Red",
    'Date'[Calendar Year] = "CY 2009"
)
ORDER BY [Value1]
Value1 Value2 Value3
1 # Sales (CY 2009 – Red) 2,038
2 # Sales (CY 2009) 39,793
3 # Sales (Red) 5,802
4 # Sales 100,231
-- ALLEXCEPT has a different behavior if used as a table function 
-- or as a CALCULATE modifier.
EVALUATE
CALCULATETABLE (
    {
         ( "CALCULATE FILTER", 
             CALCULATE ( 
                 COUNTROWS ( Sales ), 
                 ALLEXCEPT ( Sales, 'Date' ) ) 
             ),
         ( "TABLE FUNCTION", 
             COUNTROWS ( 
                 ALLEXCEPT ( Sales, 'Date' ) ) 
             )
    },
    'Date'[Calendar Year] = "CY 2009",
    'Product'[Color] = "Red"
)
Value1 Value2
CALCULATE FILTER 39,793
TABLE FUNCTION 100,231

Related articles

Learn more about ALLEXCEPT in the following articles:

Related functions

Other related functions are:

Last update: Oct 3, 2024   » Contribute   » Show contributors

Contributors: Alberto Ferrari, Marco Russo, Albe Gouws, Kenneth Barber

Microsoft documentation: https://docs.microsoft.com/en-us/dax/allexcept-function-dax

2018-2024 © SQLBI. All rights are reserved. Information coming from Microsoft documentation is property of Microsoft Corp. » Contact us   » Privacy Policy & Cookies

Context Transition

This function performs a Context Transition if called in a Row Context. Click to read more.

Row Context

This expression is executed in a Row Context. Click to read more.

Iterator

Not recommended

The use of this function is not recommended. See Remarks and Related functions for alternatives.

Not recommended

The use of this parameter is not recommended.

Deprecated

This function is deprecated. Jump to the Alternatives section to see the function to use.

Volatile

A volatile function may return a different result every time you call it, even if you provide the same arguments. Click to read more.

Deprecated

This parameter is deprecated and its use is not recommended.

DirectQuery compatibility

Limitations are placed on DAX expressions allowed in measures and calculated columns.
The state below shows the DirectQuery compatibility of the DAX function.

Contribute

Want to improve the content of ALLEXCEPT? Did you find any issue?
Please, report it us! All submissions will be evaluated for possible updates of the content.


This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.