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>]…)| Argument | Required | Description |
|---|---|---|
| table | Required | The table whose filters are removed. |
| column | Required | A 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]))| Segment | Sales Ignoring Segment |
|---|---|
| Channel Partners | 118,726,350.26 |
| Enterprise | 118,726,350.26 |
| Government | 118,726,350.26 |
| Midmarket | 118,726,350.26 |
| Small Business | 118,726,350.26 |
| Total | 118,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])))| Segment | Share of Country |
|---|---|
| Channel Partners | 1.52% |
| Enterprise | 16.52% |
| Government | 44.22% |
| Midmarket | 2.01% |
| Small Business | 35.74% |
| Total | 100.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