DAX Filter - ALLEXCEPT function


Advertisements

Description

Removes all context filters in the table except filters that have been applied to the specified columns.

Syntax

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

Parameters

Sr.No. Parameter & Description
1

table

The table over which all context filters are removed, except filters on those columns that are specified in subsequent arguments.

2

column

One or more columns that are specified for which context filters must be preserved.

For the ALLEXCEPT function, the first argument must be a reference to a base table. All the subsequent arguments must be references to base columns in that table.

You cannot use table expressions or column expressions with the ALLEXCEPT function.

Return Value

A table with all filters removed except for the filters on the specified columns.

Remarks

ALLEXCEPT function is not used by itself, but serves as an intermediate function that can be used to change the set of results over which some other calculation is performed.

You can use ALLEXCEPT function if you want to remove the filters on many, but not all, columns in a table.

Example

= CALCULATE (COUNTA (Results[Medal]), ALLEXCEPT (Hosts, Hosts[City])) 

The values in Medal column in the Results table are counted with all the filters removed, except for the filters on the Column City in the Hosts table.

dax_functions_filter.htm
Advertisements