Filter context and row context

01The problem

A colleague adds a calculated column to the customers table to show each customer's lifetime sales:

Customer Sales (wrong) =
SUMX ( orders, orders[quantity] * orders[unit_price] * ( 1 - orders[discount_pct] / 100 ) )

Every one of the 90 customers shows the same number: ₦830,541,245, the whole company's revenue. The formula is the same as the Revenue measure, which works perfectly in visuals. So why does it fail here?

The answer is evaluation context: the set of rows a formula can see when it runs. It's the single most important idea in DAX. Once it clicks, the rest of the language makes sense.

02The concept

DAX has two kinds of context.

Filter context is the set of filters active when a measure is calculated: the row and column headers of the visual, slicers, page and report filters. A measure is recalculated for every cell, each with its own filter context. In a matrix of revenue by customers[region], the "Lagos" cell is calculated with the filter region = "Lagos", which flows down to orders.

Row context means "the current row" while DAX loops through a table. It exists in two places:

• Calculated columns: the formula runs once per row of the table.
• Iterators such as SUMX, AVERAGEX and FILTER: the expression runs once per row of the table you pass in.

The crucial rule: row context doesn't filter anything. It only lets you read the current row's values, such as orders[quantity]. That's why the colleague's column fails: on the row for Kayode Distributors there's a row context on customers, but no filter, so SUMX ( orders, … ) loops over every order.

Context transition

CALCULATE turns the current row context into a filter context: "filter the model to this row". And every measure reference is wrapped in an invisible CALCULATE. So this column works:

Customer Sales = [Revenue]

On Kayode's row, [Revenue] becomes CALCULATE ( [Revenue] ), which filters customers to Kayode, and that filter flows to orders. That's context transition, and it's why referencing a measure inside a row context behaves so differently from writing the same formula out in full.

03Example

Two calculated columns on customers. The first uses context transition, and the second uses it to group customers into bands:

Customer Sales = [Revenue]

Customer Band =
SWITCH (
    TRUE (),
    customers[Customer Sales] >= 20000000, "A: ₦20m+",
    customers[Customer Sales] >= 10000000, "B: ₦10m–20m",
    customers[Customer Sales] > 0, "C: under ₦10m",
    "No sales"
)

SWITCH ( TRUE (), … ) checks each condition in order and returns the first one that's true. It's DAX's tidy alternative to nested IFs.

Because Customer Band is a column, you can put it on an axis or in a slicer. A measure can't do that: measures give numbers for a filter context; columns create the categories you filter by.

04Walkthrough

1. On customers, add the wrong column from the problem and confirm every row shows ₦830,541,245.
2. Change it to Customer Sales = [Revenue]. Each customer now has its own number. Delete the wrong column.
3. Add Customer Band from the example and format Customer Sales as currency.
4. Build a table visual: customers[Customer Band], [Active Customers], [Revenue]. You've just used a column (the band) to slice measures.
5. Add a slicer on Date[Year] and select 2026. Revenue changes, but the bands don't, because they were calculated at refresh from lifetime sales.

05Practice

06More practice

07Check your understanding