FILTER, ALL, ALLEXCEPT & REMOVEFILTERS
Power BI · DAX (Data Analysis Expressions)
Theory
These functions control which rows a calculation sees. FILTER(table, condition) is an iterator that returns the rows of a table that meet a condition; inside CALCULATE it lets you filter on expressions or measures, for example FILTER(Customers, [Total Sales] > 1000). ALL(table or columns) returns all rows ignoring filters, and as a CALCULATE argument it removes filters. REMOVEFILTERS does the same removal and reads more clearly, but can only be used as a CALCULATE modifier. ALLEXCEPT(table, columns) removes all filters on a table except those on the listed columns.
The classic use is percentage of total: DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Sales[Region]))). For North the numerator is ₹3,200 and the denominator ignores the Region filter, ₹6,200, so the share is 51.6%; West is 38.7% and South 9.7%. ALLSELECTED is a related function that respects the user's outer slicer selections.
Why we use it
Ratios, shares and benchmarks need a denominator that ignores some filters. FILTER handles conditions that a simple column filter cannot express.
When to use it
Use REMOVEFILTERS or ALL for grand-total denominators, ALLEXCEPT to keep one grouping (such as Category) while ignoring the rest, and FILTER only when the condition needs a measure or complex logic. Filtering a whole large table with FILTER is slow; filter a column instead.
How it works
- Need a denominator or special filter
- Should some filters be ignored? Yes: Use ALL / REMOVEFILTERS / ALLEXCEPT in CALCULATE. No: Keep the visual's filters.
- Does the condition need a measure or complex logic? Yes: Wrap it in FILTER over a small table. No: Use a simple column predicate.
- Divide or compare with the normal measure
- Shares and benchmarks per cell
Syntax
FILTER(<table>, <filter>)
ALL([<table> | <column>[, <column>[, …]]])
ALLEXCEPT(<table>, <column>[, <column>[, …]])
REMOVEFILTERS([<table> | <column>[, <column>[, …]]])
ALLSELECTED([<tableName> | <columnName>[, …]])Example
% of Total Sales =
DIVIDE(
[Total Sales],
CALCULATE([Total Sales], REMOVEFILTERS(Sales[Region]))
)
// Share within the product's own category
% of Category = DIVIDE([Total Sales], CALCULATE([Total Sales], ALLEXCEPT(Products, Products[Category])))
// Sales from customers with more than ₹1,000 of sales
Big Customer Sales = CALCULATE([Total Sales], FILTER(Customers, [Total Sales] > 1000)) // 2,700 + 2,400 = 5,100Key points
- FILTER returns a filtered table; it is an iterator
- ALL / REMOVEFILTERS remove filters inside CALCULATE
- ALLEXCEPT keeps filters only on the listed columns
- ALLSELECTED keeps the user's slicer selections
- Prefer column filters over FILTER on a whole fact table