DAX CALCULATE Function Explained Step by Step

CALCULATE is the most important function in DAX and the one that confuses people longest. Microsoft's reference describes it in one line: it evaluates an expression in a modified filter context (DAX reference: CALCULATE). Everything else follows from understanding what "filter context" is and exactly how CALCULATE modifies it. This article works through it step by step, with the value each measure returns on real sales data.

Filter context in one paragraph

Every number in a Power BI visual is calculated under a set of filters: the row and column of the visual, slicers, and page and report filters. That set is the filter context. A measure such as total revenue has one formula but returns a different value in each cell, because each cell has a different filter context. CALCULATE lets a measure change that context before evaluating.

Seeing filter context with real numbers

DAX needs Power BI to run, so to show exactly what each measure returns, the code below reproduces the logic in pandas on a real retailer's sales (UCI: Online Retail). A filter context is simply a true or false mask over the sales rows, and revenue sums quantity times price for the rows the mask keeps, returning blank when it keeps none, just as DAX does:

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)]

def revenue(filter_context):
    rows = sales[filter_context]
    return "(blank)" if rows.empty else round((rows["Quantity"] * rows["UnitPrice"]).sum() / 1000, 1)

everything = pd.Series(True, index=sales.index)
print("Revenue with no filters, in thousands:", revenue(everything))

Revenue with no filters, in thousands: 10271.4

The DAX measure this stands for is the usual starting point:

Revenue = SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] )

Steps 1 to 3: add, keep and remove filters

Imagine a table visual with one row per country and four measures. For simplicity, Country is a column of the Sales table here; in a star schema it would sit in a Customer dimension and behave the same way:

UK Revenue =
CALCULATE ( [Revenue], Sales[Country] = "United Kingdom" )

UK Revenue KEEPFILTERS =
CALCULATE ( [Revenue], KEEPFILTERS ( Sales[Country] = "United Kingdom" ) )

% of All Countries =
DIVIDE ( [Revenue], CALCULATE ( [Revenue], REMOVEFILTERS ( Sales[Country] ) ) )

Here is what each returns, row by row:

countries = ["United Kingdom", "Netherlands", "EIRE", "Germany", "France"]
visual = []
for country in countries:
    row_filter = sales["Country"] == country
    uk = sales["Country"] == "United Kingdom"
    visual.append({
        "Country": country,
        "Revenue": revenue(row_filter),
        "UK Revenue": revenue(uk),
        "UK Revenue KEEPFILTERS": revenue(row_filter & uk),
        "% of All Countries": f"{revenue(row_filter) / revenue(everything):.1%}",
    })
print(pd.DataFrame(visual).to_string(index=False))

       Country  Revenue  UK Revenue UK Revenue KEEPFILTERS % of All Countries
United Kingdom   8747.6      8747.6                 8747.6              85.2%
   Netherlands    283.9      8747.6                (blank)               2.8%
          EIRE    271.2      8747.6                (blank)               2.6%
       Germany    205.6      8747.6                (blank)               2.0%
        France    184.6      8747.6                (blank)               1.8%

Step 1: a filter argument overwrites. "UK Revenue" shows 8,747.6 on every row, including the Netherlands. The visual's row filters Country to the Netherlands; CALCULATE's filter argument is also on Country, and the reference is explicit: when a column is already in the filter context, the existing filter is overwritten by the new one. This surprises nearly everyone at first, and it is exactly what makes CALCULATE useful for comparisons against a fixed reference.

Step 2: KEEPFILTERS intersects. Wrapping the condition in KEEPFILTERS keeps the visual's filter and adds the new one on top (DAX reference: KEEPFILTERS). A row can only be the Netherlands and the United Kingdom at the same time if it is empty, so every row except the UK returns blank. Use KEEPFILTERS when a measure should respect what the user has selected.

Step 3: REMOVEFILTERS for shares. The denominator of "% of All Countries" removes the Country filter, so it always returns total revenue, 10,271.4, and dividing each row's revenue by it gives the share. Microsoft recommends REMOVEFILTERS over ALL for this purpose where supported. DAX FILTER and ALL functions covers the variants that remove filters from some columns and keep others.

Step 4: context transition in calculated columns

A calculated column is evaluated row by row: it has a row context but no filter context. That creates a classic trap. In a Customer table related to Sales, a column defined as SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] ) returns total revenue for all customers on every row, because nothing filters the Sales table. Wrapping it in CALCULATE turns the current row into a filter, which the reference calls context transition:

Customer Revenue = CALCULATE ( SUMX ( Sales, Sales[Quantity] * Sales[UnitPrice] ) )
Segment = IF ( Customer[Customer Revenue] < 1000, "Low", "High" )

customers = pd.DataFrame({"CustomerID": sales["CustomerID"].dropna().unique()})
customers["Without CALCULATE"] = revenue(everything)
customers["With CALCULATE"] = [revenue(sales["CustomerID"] == c) for c in customers["CustomerID"]]
customers["Segment"] = customers["With CALCULATE"].map(lambda r: "Low" if r < 1 else "High")
print(customers.head(3).astype({"CustomerID": "int64"}).to_string(index=False))
print(customers["Segment"].value_counts().to_string())

 CustomerID  Without CALCULATE  With CALCULATE Segment
      17850            10271.4             5.4    High
      13047            10271.4             3.2    High
      12583            10271.4             6.7    High
Segment
Low     2631
High    1703

Without CALCULATE, every customer shows the grand total, and every customer would be "High". With it, each shows their own revenue, in thousands, and the segments split 2,631 to 1,703. Measures used inside a row context get context transition automatically, which is why referencing [Revenue] in a calculated column behaves like the CALCULATE version.

The rules for Boolean filters

A Boolean filter such as Sales[Country] = "United Kingdom" is shorthand, and the reference sets limits on it: it can reference columns from only one table, cannot reference measures, cannot use a nested CALCULATE, and cannot use functions that scan or return a table unless they are inside an aggregation. When a condition breaks these rules, use a table filter built with FILTER over the smallest possible table or column.

Filter modifier functions at a glance

USERELATIONSHIP is how role-playing dates work in a star schema, and time intelligence in DAX builds on CALCULATE with date filters.

Common mistakes

• Expecting a filter argument to intersect with the visual's filter, as in step 1.
• Filtering a whole table with FILTER when a column filter would do; it is slower and can keep unwanted filters.
• Repeating the same CALCULATE expression several times in one measure; store it once with a variable, as shown in DAX variables.
• Forgetting context transition in calculated columns and iterators.

For the concepts underneath, measures, calculated columns and the two kinds of context, see the DAX guide. DAX fundamentals and advanced formulas are part of the DAX module of the Power BI course.

Sources

• CALCULATE function (DAX reference, Microsoft Learn) — Microsoft Learn
• KEEPFILTERS function (DAX reference, Microsoft Learn) — Microsoft Learn
• Online Retail (UCI Machine Learning Repository) — UCI Machine Learning Repository