ALLEXCEPT

Removes every filter on a table except those on the columns named.

Group: Filter. Compatible with DAX.

Syntax

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

Returns

Table. As a filter of CALCULATE, the table's filters taken away except those on the columns listed.

Remarks

• ALLEXCEPT ( Products, Products[Category] ) lets a product be compared with its whole category: the filter on the product goes, the one on the category stays.
• The filters of the tables the first one reaches go as well: ALLEXCEPT ( Sales, Sales[Shop] ) also removes a filter on Places[Kind], although it is a column of Places.
• A listed column may belong to such a table: ALLEXCEPT ( Sales, Products[Category] ) keeps only the category's filter.
• It keeps a filter only where there is one; it never adds one.
• The first argument must be a table of the model, not a table expression.

Examples

Each product's share of its category

EVALUATE
SUMMARIZECOLUMNS (
    Products[Category],
    Products[Product],
    "Liters", [Liters sold],
    "Share of category", DIVIDE ( [Liters sold], CALCULATE ( [Liters sold], ALLEXCEPT ( Products, Products[Category] ) ) )
)
ORDER BY Products[Category], Products[Product]

A filter on a related table goes too

The rows group by Places[Kind]. Sales reaches Places, so that filter is removed with the others, and both rows show every liter; the depot's row appears only because of it.

EVALUATE
SUMMARIZECOLUMNS (
    Places[Kind],
    "Liters", [Liters sold],
    "ALLEXCEPT ( Sales, Sales[Shop] )", CALCULATE ( [Liters sold], ALLEXCEPT ( Sales, Sales[Shop] ) )
)
ORDER BY Places[Kind]

Related functions

ALL, REMOVEFILTERS, VALUES, CALCULATE, ALLSELECTED.

Last updated 3 November 2026 · OpenBI Studio 0.3.74