Downloads
DAXFilter and contextIntermediate

ALLEXCEPT

Removes every filter on a table except the filters on the columns you list.

Syntax
ALLEXCEPT(<table>, <column> [, <column> ...])

Returns: A table. Used inside CALCULATE, it keeps only the named column filters.

What it does

ALLEXCEPT clears all the filters on a table except the ones on the columns you name. Use it to compare an item with its own group: a store with its country, or a product with its category. The grouping column keeps its filter, and every other column in the table is cleared.

Syntax and arguments

Example

A report lists stores. Each store's share should be measured against all stores in the same country.

Share of Country Sales =
DIVIDE (
    [Total Sales],
    CALCULATE ( [Total Sales], ALLEXCEPT ( Stores, Stores[Country] ) )
)

The Country filter stays because it is named. The store name and store type filters are cleared, so the denominator is the country total. Within one country, the store shares add up to 100 percent.

Example: a second use

Each product's share of its own category.

Share of Category Sales =
DIVIDE (
    [Total Sales],
    CALCULATE ( [Total Sales], ALLEXCEPT ( Product, Product[ProductCategory] ) )
)

The category filter stays, and the product name filter is cleared. This shows mix within a category, which is what a product manager usually wants to see.

Things to watch

In business terms

Shares within a segment: a branch's share of its region, an account's share of its class, a cost center's share of its department. Budget and variance packs use it to show mix inside each group while keeping the group total as the base.

Related functions

All DAX functions · View as Markdown · DAX fundamentals