DAX ALL vs ALLEXCEPT vs ALLSELECTED vs REMOVEFILTERS
· 8 min read
DAX ALL removes filters. Inside CALCULATE, ALL ( Customers ) makes the expression ignore every filter on the Customers table, which gives you a grand total for a percentage. The other three are variations: REMOVEFILTERS does the same as ALL inside CALCULATE (and nothing else), ALLEXCEPT removes all filters on a table except the columns you list, and ALLSELECTED removes the filters from the visual but keeps slicers and page filters.
% of Grand Total =
DIVIDE ( [Completed Revenue], CALCULATE ( [Completed Revenue], ALL ( Customers ) ) )
% of Region =
DIVIDE ( [Completed Revenue], CALCULATE ( [Completed Revenue], ALLEXCEPT ( Customers, Customers[Region] ) ) )
% of Selection =
DIVIDE ( [Completed Revenue], CALCULATE ( [Completed Revenue], ALLSELECTED ( Customers ) ) )This guide runs all four on the same small Brightline Supply Co. model, shows the numbers each one returns, and ends with the ALLEXCEPT mistake AI assistants make most often. If CALCULATE itself is new to you, read DAX CALCULATE explained first. This post assumes you know how a filter argument works.
The example model
Brightline sells office, kitchen and cleaning supplies to businesses. The model is a star schema: a Sales fact table with eight order lines, plus Products, Customers and Date dimensions. Six lines are completed, which is the only status Brightline counts as revenue:
Completed Revenue =
CALCULATE (
SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] * (1 - Sales[DiscountPct]) ),
Sales[Status] = "completed"
)| Customer | Segment | Region | Completed Revenue |
|---|---|---|---|
| Harbor & Pine Hotels | Enterprise | West | 208.10 |
| River Print Co | SMB | West | 180.00 |
| Lakeside Studios | SMB | East | 149.00 |
| Pioneer Studios | Mid-Market | North | 75.00 |
| Total | 612.10 |
ALL and ALLEXCEPT: % of grand total and % of region
Build a matrix with Customers[Region] and then Customers[CustomerName] on the rows, and add the first two measures from the top:
% of Grand Total =
DIVIDE ( [Completed Revenue], CALCULATE ( [Completed Revenue], ALL ( Customers ) ) )
% of Region =
DIVIDE ( [Completed Revenue], CALCULATE ( [Completed Revenue], ALLEXCEPT ( Customers, Customers[Region] ) ) )| Region | Customer | Completed Revenue | % of Grand Total | % of Region |
|---|---|---|---|---|
| West | (subtotal) | 388.10 | 63.4% | 100.0% |
| West | Harbor & Pine Hotels | 208.10 | 34.0% | 53.6% |
| West | River Print Co | 180.00 | 29.4% | 46.4% |
| East | (subtotal) | 149.00 | 24.3% | 100.0% |
| East | Lakeside Studios | 149.00 | 24.3% | 100.0% |
| North | (subtotal) | 75.00 | 12.3% | 100.0% |
| North | Pioneer Studios | 75.00 | 12.3% | 100.0% |
| Total | 612.10 | 100.0% | 100.0% |
What happens in the Harbor & Pine Hotels row:
- The filter context is Region = West and CustomerName = Harbor & Pine Hotels.
ALL ( Customers )removes both filters, so the denominator is 612.10. 208.10 / 612.10 = 34.0%.ALLEXCEPT ( Customers, Customers[Region] )removes every Customers filter except Region. The West filter stays, so the denominator is 388.10. 208.10 / 388.10 = 53.6%.
On the grand total row there is no Region filter to keep, so ALLEXCEPT also returns 612.10 and shows 100%.
In SQL, the same two denominators are window functions: SUM(revenue) OVER () for the grand total and SUM(revenue) OVER (PARTITION BY region) for the region. See SQL window functions if that syntax is new.
ALLSELECTED: percent of what the user selected
Now add a slicer on Customers[Region] and select West and East. Put CustomerName on the rows of a table visual and compare ALL with ALLSELECTED:
% with ALL =
DIVIDE ( [Completed Revenue], CALCULATE ( [Completed Revenue], ALL ( Customers ) ) )
% with ALLSELECTED =
DIVIDE ( [Completed Revenue], CALCULATE ( [Completed Revenue], ALLSELECTED ( Customers ) ) )| Customer | Completed Revenue | % with ALL | % with ALLSELECTED |
|---|---|---|---|
| Harbor & Pine Hotels | 208.10 | 34.0% | 38.7% |
| River Print Co | 180.00 | 29.4% | 33.5% |
| Lakeside Studios | 149.00 | 24.3% | 27.7% |
| Total | 537.10 | 87.7% | 100.0% |
ALL ( Customers ) ignores the slicer too, so the denominator is still 612.10 and the visible rows add up to 87.7%. ALLSELECTED ( Customers ) removes the row filters that come from the visual but keeps the slicer, so the denominator is 537.10 and the total is 100% (the rows add to 99.9% only because of rounding).
Which one is right depends on the question. "What share of all Brightline revenue is Harbor?" is ALL. "What share of the regions I am looking at?" is ALLSELECTED. Ask before you write the measure.
REMOVEFILTERS: the clearer way to write ALL
Inside CALCULATE, these two lines do exactly the same thing:
CALCULATE ( [Completed Revenue], ALL ( Customers ) )
CALCULATE ( [Completed Revenue], REMOVEFILTERS ( Customers ) )REMOVEFILTERS can only be used as a CALCULATE modifier. ALL can also be a table function that returns rows. That second use is why ALL still matters:
Customer Count (all) =
COUNTROWS ( ALL ( Customers ) )This returns 4 on every row, whatever the slicer says. You also need ALL as a table in iterators such as RANKX, where RANKX ( ALL ( Customers[CustomerName] ), [Completed Revenue] ) ranks each customer against every customer. That is covered in RANKX in DAX.
A practical rule: write REMOVEFILTERS when you mean "ignore these filters", and keep ALL for when you need a table back. The intent is easier to read in six months.
ALL () and REMOVEFILTERS () with no arguments remove every filter in the model. That is rarely what you want for a percentage, because it also drops date and product filters.
Which one to use
| Goal | Function | Denominator in the examples |
|---|---|---|
| Share of the grand total, ignoring every filter | REMOVEFILTERS ( Customers ) or ALL ( Customers ) | 612.10 |
| Share of the parent group (customer within region) | ALLEXCEPT ( Customers, Customers[Region] ), or better REMOVEFILTERS ( Customers[CustomerName] ) | 388.10 for West |
| Share of what the slicers leave visible | ALLSELECTED ( Customers ) | 537.10 with West and East selected |
| A table of all rows, for COUNTROWS or RANKX | ALL ( ... ) | 4 customers |
The "or better" in the second row is explained in the next section.
Common mistakes
ALLEXCEPT throws away the user's slicers
Ask an AI assistant for "each customer's share of their region" and you will usually get the ALLEXCEPT measure from the top of this page. It works until someone adds a slicer on another Customers column. Put a Segment slicer on the page, select SMB, and compare it with a version that removes only the customer filter:
-- AI version
% of Region (ALLEXCEPT) =
DIVIDE ( [Completed Revenue], CALCULATE ( [Completed Revenue], ALLEXCEPT ( Customers, Customers[Region] ) ) )
-- Fix
% of Region (REMOVEFILTERS) =
DIVIDE ( [Completed Revenue], CALCULATE ( [Completed Revenue], REMOVEFILTERS ( Customers[CustomerName] ) ) )| Region | Customer | Completed Revenue | % of Region (ALLEXCEPT) | % of Region (REMOVEFILTERS) |
|---|---|---|---|---|
| West | River Print Co | 180.00 | 46.4% | 100.0% |
| West | (subtotal) | 180.00 | 46.4% | 100.0% |
| East | Lakeside Studios | 149.00 | 100.0% | 100.0% |
| Total | 329.00 | 53.7% | 100.0% |
River Print Co is the only SMB customer in West, so it should be 100% of the visible West total. ALLEXCEPT removed every Customers filter except Region, including the Segment slicer. The denominator went back to 388.10, which includes Harbor, an Enterprise customer the user filtered out.
The fix removes only the filter you mean to remove: the customer filter from the row. The Region row and the Segment slicer stay. How to spot the bug: change a slicer on the same table and check that the subtotal row still shows 100%. If it does not, the measure is clearing more than the row filter.
Removing filters from the wrong column
ALL ( Customers[Segment] ) with CustomerName on the rows changes nothing, and every row shows 100%. The column or table inside ALL, ALLEXCEPT or REMOVEFILTERS must cover the column the visual groups by. The same mistake with categories is in the CALCULATE post.
ALL on the fact table
ALL ( Sales ) also removes filters that reach Sales from Products, Customers and Date, because it covers the expanded table (Sales plus the tables it relates to). A "% of region" measure written with CALCULATE ( [Completed Revenue], ALL ( Sales ) ) as the denominator quietly returns the share of the grand total instead: Harbor shows 34.0%, not 53.6%, because the Region filter is gone too. Remove filters from the dimension column you are grouping by, not from the whole fact table.
FAQ
What does ALL do in DAX?
As a CALCULATE filter, ALL removes filters from the table or columns you name. On its own, it returns a table with all rows (or all distinct values of the columns), ignoring filters. That second form is used inside COUNTROWS, FILTER and RANKX.
What is the difference between ALL and REMOVEFILTERS?
Inside CALCULATE there is no difference in the result. REMOVEFILTERS cannot return a table, so it only does the "remove filters" job, which makes the measure easier to read.
What is the difference between ALL and ALLSELECTED?
ALL ignores every filter on the column or table, including slicers. ALLSELECTED ignores the filters created by the visual's rows and columns but keeps slicers and page filters. Use ALLSELECTED when percentages should add up to 100% of what the user selected.
When should I use ALLEXCEPT?
When you want to keep filters on a few columns and drop everything else on that table, and you are sure no slicer on another column of the table should apply. For "share of the parent group", REMOVEFILTERS on the child column is usually safer.
Is there an SQL equivalent of ALL in DAX?
The closest is a window function with an empty or partial OVER clause: SUM(revenue) OVER () behaves like ALL on the grouping columns, and OVER (PARTITION BY region) behaves like ALLEXCEPT on region. More side-by-side examples are in DAX vs SQL.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Each category's share of revenue” →