DAX ALLEXCEPT function

Removes all filters from a table except the filters on the columns you list.

ALLEXCEPT is a shortcut used inside CALCULATE. Instead of naming every filter to remove, you name the few to keep. It is common in "share of group" measures.

Syntax

ALLEXCEPT(<table>, <column>[, <column>]…)
ArgumentRequiredDescription
tableRequiredThe table whose filters are removed.
columnRequiredA column whose filter is kept. List as many as you need.

Returns: A table, used as a filter inside CALCULATE. It is not a value you can show on its own.

Examples

Calculated on the free practice data (table financials), with [Total Sales] = SUM(financials[Sales]) and [Total Profit] = SUM(financials[Profit]).

1. Keep only the Country filter

Sales Ignoring Segment = CALCULATE([Total Sales], ALLEXCEPT(financials, financials[Country]))
SegmentSales Ignoring Segment
Channel Partners118,726,350.26
Enterprise118,726,350.26
Government118,726,350.26
Midmarket118,726,350.26
Small Business118,726,350.26
Total118,726,350.26

The Segment filter from each row is removed, and only a Country filter would stay. No country is selected here, so every row shows total sales.

2. Share of the selected country

Share of Country = DIVIDE([Total Sales], CALCULATE([Total Sales], ALLEXCEPT(financials, financials[Country])))
SegmentShare of Country
Channel Partners1.52%
Enterprise16.52%
Government44.22%
Midmarket2.01%
Small Business35.74%
Total100.00%

Each segment divided by everything allowed by the Country filter. Add a Country slicer, and it becomes the share within that country.

Try ALLEXCEPT yourself

Change the formula and run it on the 700-row practice data. Free, with no signup.

Want an exercise that checks your answer? Free DAX practice

Good to know

  • If you add a new filter column to a report later, ALLEXCEPT removes it too. Use REMOVEFILTERS when you want to clear only specific columns.
  • The columns you keep must belong to the table you name, or be reachable from it through a relationship.

Related functions

Official reference: ALLEXCEPT function on Microsoft Learn