DAX FILTER and ALL Functions Explained

Most useful DAX measures compare a number with something else: a total, a previous period, a subset that meets a condition. Two function families do most of that work inside CALCULATE. ALL and its relatives remove filters; FILTER returns the rows that meet a condition. Knowing exactly which filters each one removes or adds is what makes a percentage measure right, and the differences are easiest to see with real numbers.

The data and the measures

The numbers below come from a UK online retailer's sales for January to September 2011 (UCI: Online Retail). DAX needs Power BI to run, so the pandas code reproduces what each measure returns: each filter context is a mask over the sales rows. For simplicity, Country and Quarter are columns of one Sales table.

import io
import urllib.request
import zipfile
import pandas as pd

url = "https://archive.ics.uci.edu/static/public/352/online+retail.zip"
with zipfile.ZipFile(io.BytesIO(urllib.request.urlopen(url).read())) as archive:
    raw = pd.read_excel(archive.open("Online Retail.xlsx"), dtype={"InvoiceNo": str, "StockCode": str})
services = {"POST", "DOT", "M", "m", "C2", "D", "S", "B", "BANK CHARGES", "AMAZONFEE", "CRUK"}
sales = raw[~raw["InvoiceNo"].str.startswith("C") & ~raw["StockCode"].isin(services)
            & ~raw["StockCode"].str.startswith("gift_") & (raw["Quantity"] > 0) & (raw["UnitPrice"] > 0)
            & (raw["InvoiceDate"] >= "2011-01-01") & (raw["InvoiceDate"] < "2011-10-01")]
sales = sales.assign(Quarter="Q" + sales["InvoiceDate"].dt.quarter.astype(str),
                     Month=sales["InvoiceDate"].dt.strftime("%Y-%m"))

def revenue(filter_context):
    rows = sales[filter_context]
    return (rows["Quantity"] * rows["UnitPrice"]).sum()

no_filters = pd.Series(True, index=sales.index)
print(f"total revenue, January to September 2011: {revenue(no_filters) / 1000:.1f} thousand")

total revenue, January to September 2011: 6313.6 thousand

ALL: three different shares from one matrix

Picture a matrix with countries on rows and quarters on columns. Each cell's filter context is one country and one quarter. Three measures divide the cell's revenue by three different denominators:

% of All =
DIVIDE ( [Revenue], CALCULATE ( [Revenue], ALL ( Sales ) ) )

% of Country =
DIVIDE ( [Revenue], CALCULATE ( [Revenue], ALL ( Sales[Quarter] ) ) )

% of Quarter =
DIVIDE ( [Revenue], CALCULATE ( [Revenue], ALL ( Sales[Country] ) ) )

cells = []
for country in ["Netherlands", "EIRE", "Germany"]:
    for quarter in ["Q1", "Q2", "Q3"]:
        cell = (sales["Country"] == country) & (sales["Quarter"] == quarter)
        cells.append({
            "Country": country, "Quarter": quarter,
            "Revenue": round(revenue(cell) / 1000, 1),
            "% of All": f"{revenue(cell) / revenue(no_filters):.2%}",
            "% of Country": f"{revenue(cell) / revenue(sales['Country'] == country):.1%}",
            "% of Quarter": f"{revenue(cell) / revenue(sales['Quarter'] == quarter):.1%}",
        })
print(pd.DataFrame(cells).to_string(index=False))

    Country Quarter  Revenue % of All % of Country % of Quarter
Netherlands      Q1     71.6    1.13%        36.3%         3.8%
Netherlands      Q2     58.6    0.93%        29.7%         2.9%
Netherlands      Q3     67.2    1.06%        34.0%         2.7%
       EIRE      Q1     52.1    0.83%        26.0%         2.8%
       EIRE      Q2     45.7    0.72%        22.8%         2.3%
       EIRE      Q3    102.8    1.63%        51.2%         4.2%
    Germany      Q1     37.0    0.59%        27.9%         2.0%
    Germany      Q2     46.6    0.74%        35.2%         2.3%
    Germany      Q3     48.9    0.78%        36.9%         2.0%

• The whole table: ALL ( Sales ) removes every filter on the table, so the denominator is the grand total. Ireland's third quarter is 1.63% of all revenue.
• The quarter column: ALL ( Sales[Quarter] ) removes only the quarter filter and keeps the country, as the reference describes: other filters still apply (DAX reference: ALL). The denominator is the country's nine-month total, and Ireland's third quarter turns out to be 51.2% of its year so far.
• The country column: ALL ( Sales[Country] ) removes the country filter and keeps the quarter, so the denominator is the quarter's total across all countries. Here, Ireland's third quarter is just 4.2%.

Same cell, three true statements, three different stories. The choice of denominator is a business decision; name the measure so readers know which one they are seeing.

ALLEXCEPT, ALLSELECTED and REMOVEFILTERS

• ALLEXCEPT removes all filters on a table except those on the listed columns. ALL ( Sales[Quarter] ) removes only the quarter; ALLEXCEPT ( Sales, Sales[Country] ) would also remove any slicer on product or channel columns of the Sales table. Use it when you want "everything except these".
• ALLSELECTED ignores the filters coming from the visual itself but keeps the ones from outside it, such as slicers (DAX reference: ALLSELECTED). The quarter denominators above include every country, including the UK, even though the matrix shows only three. If a user slices to these three countries, ALLSELECTED shares add up to 100% across what they selected, while ALL shares do not.
• REMOVEFILTERS does what ALL does inside CALCULATE, but only that, and Microsoft recommends it where supported when the intent is simply to clear filters. DAX CALCULATE explained shows it in a share-of-total measure.

ALL also works as a table function outside CALCULATE: COUNTROWS ( ALL ( Sales[Country] ) ) counts every country regardless of filters. The reference also warns about auto-exist: when several columns of one table are filtered at once, ALL can return results based on existing combinations rather than all values. If a share looks wrong with multiple slicers on the same table, that is a likely cause.

FILTER: when a simple condition is not enough

FILTER returns a table containing the rows of another table that meet a condition (DAX reference: FILTER). Inside CALCULATE, that table becomes a filter. It is needed when the condition cannot be written as a simple Boolean filter, above all when it depends on a measure. For example, revenue from busy months only, defined as months with more than 1,500 orders:

Orders = DISTINCTCOUNT ( Sales[InvoiceNo] )

Revenue in Busy Months =
CALCULATE (
    [Revenue],
    FILTER ( VALUES ( Sales[Month] ), [Orders] > 1500 )
)

orders = sales.groupby("Month")["InvoiceNo"].nunique()
busy = orders[orders > 1500].index
print("busy months:", ", ".join(busy))
print(f"revenue in busy months: {revenue(sales['Month'].isin(busy)) / 1000:.1f} of {revenue(no_filters) / 1000:.1f} thousand")

busy months: 2011-05, 2011-06, 2011-09
revenue in busy months: 2510.4 of 6313.6 thousand

FILTER iterates the months, evaluates the Orders measure for each, and keeps three. Note that it iterates VALUES ( Sales[Month] ), a small one-column table, not the whole Sales table.

Boolean filters first, FILTER when needed

Microsoft's guidance is direct: pass filter arguments as Boolean expressions whenever possible, because imported tables are column stores optimised to filter columns, and use FILTER only when necessary, for conditions involving measures, comparisons between columns, or OR logic (Microsoft Learn: avoid FILTER as a filter argument). The guidance shows both forms side by side:

-- slower: iterates the Product table and replaces its filters
Red Sales = CALCULATE ( [Sales], FILTER ( 'Product', 'Product'[Color] = "Red" ) )

-- preferred: a column filter that keeps existing colour filters
Red Sales = CALCULATE ( [Sales], KEEPFILTERS ( 'Product'[Color] = "Red" ) )

When FILTER is necessary, filter the smallest table that does the job: a single column with VALUES or ALL, not an entire fact table.

Choosing the right function

Common mistakes

• Using ALL on the whole table when only one column's filter should go, which also discards slicers on other columns.
• Expecting ALL to respect slicers; use ALLSELECTED for shares of what the user selected.
• Filtering a fact table with FILTER for a condition a Boolean filter could express.
• Hiding repeated expressions in long measures; DAX variables make them readable.

For the concepts underneath, see the DAX guide. DAX aggregate functions and advanced formulas are part of the DAX module of the Power BI course.

Sources

• ALL function (DAX reference, Microsoft Learn) — Microsoft Learn
• ALLSELECTED function (DAX reference, Microsoft Learn) — Microsoft Learn
• FILTER function (DAX reference, Microsoft Learn) — Microsoft Learn
• Avoid using FILTER as a filter argument in DAX (Microsoft Learn) — Microsoft Learn
• Online Retail (UCI Machine Learning Repository) — UCI Machine Learning Repository