CALCULATE and Filter Context in DAX, Explained on Sixteen Rows

1. What filter context is, in one sentence

Before the definition: you put a measure Total Revenue = SUM(Orders[Revenue]) into a table sliced by product. It shows 5,060 next to Desk and 3,230 next to Chair. Say what made those two numbers different, given that the measure never mentions Product.

The visual did. Filter context is the set of filters in force when a measure is evaluated, and it is assembled from everything on the report: the row the measure is sitting in, the columns, the slicers, the page filters, the cross-highlighting from another visual. The measure does not know or care where they came from. It just runs against whatever rows survive.

So a measure has no fixed answer. SUM(Orders[Revenue]) is 9,890 with nothing filtered, 5,060 in the Desk row, 3,040 in the East row, and 1,760 in a cell where Desk and East cross. One formula, one meaning, and a different number in every cell, which is the entire point of a measure and is covered from the other side in columns vs measures.

The useful mental picture is a set of allowed values per column. Before any filtering, every column allows everything. A table sliced by product puts Product into that set with exactly one allowed value in each row. CALCULATE is the function that edits the set.

2. The rule: added, or overwritten

Before the rule: you write CALCULATE([Total Revenue], Products[Product] = "Desk") and drop it into a table sliced by product. Predict what appears in the Chair row, before reading on.

5,060, the same as the Desk row, and it is not a bug. Microsoft's reference page states the rule in two lines, and it is worth reading slowly:

In the Chair row, Product is already in the filter context, restricted to Chair. Your filter names the same column, so it overwrites: Product is now restricted to Desk. The measure runs and returns Desk's 5,060, and it does that in every row of the table, because every row's Product filter is overwritten by the same one.

Say out loud why the total row also shows 5,060 rather than 9,890. The total row has no Product filter of its own, so there is nothing to overwrite. The Desk filter is added instead, and the answer is Desk's total. Same rule, and this time the "added" branch of it.

Total Revenue = SUM ( Orders[Revenue] )

Desk Revenue =
CALCULATE ( [Total Revenue], Products[Product] = "Desk" )

3. The same measure in two visuals

Before the table: take that identical Desk Revenue measure and put it in a table sliced by region instead. Predict whether every row shows 5,060 again.

They do not, and this is the moment the rule stops being a fact and becomes useful.

Here the visual filters Region, and the measure filters Product. Different columns, so nothing is overwritten and both filters apply. East's 1,760 is East's two desk orders, 660 plus 1,100. And the four regional figures add to 5,060, which is the check that both filters are really in force.

One measure, written once, behaving two ways. Neither behaviour is special-cased; both come out of the same sentence in the documentation. This is why "CALCULATE overwrites filters" and "CALCULATE adds filters" are both things people say and both are half the rule.

Now picture a measure in your own model that returns the same number in every row of one visual and sensible numbers in another. Which column is it filtering, and is that the column the broken visual is sliced by?

4. KEEPFILTERS, for when you wanted both

Before the fix: in the product table, you actually wanted the Chair row to show desk-and-chair revenue, which is nothing, rather than all desks. Say what you need CALCULATE to do differently.

You need it to intersect rather than replace, and there is a function whose entire job is that. Microsoft lists KEEPFILTERS among the filter modifiers with the description "Add filter without removing existing filters on the same columns."

Desk Revenue Kept =
CALCULATE ( [Total Revenue], KEEPFILTERS ( Products[Product] = "Desk" ) )

The Chair row now asks for rows that are both Chair and Desk. There are none, the measure has nothing to sum, and DAX returns blank rather than zero. Blank rows are hidden by default in most visuals, which is usually what you wanted.

Which of the two is correct depends entirely on the question. "How much do desks bring in, regardless of what this row is about?" is the first measure, and it is genuinely what you want as the denominator of a share. "How much of this row is desks?" is the second. Deciding which sentence you meant is the work; the function is just the switch.

5. Removing filters: percent of total

Before the technique: you want each region's share of total revenue. The numerator is easy. Say what makes the denominator hard, before reading on.

The denominator has to ignore the row it is sitting in. In the East row, the filter context restricts Region to East, and you need a number computed as if it did not. That is what REMOVEFILTERS is for.

Revenue % of Total =
DIVIDE (
    [Total Revenue],
    CALCULATE ( [Total Revenue], REMOVEFILTERS ( Orders[Region] ) )
)

Three notes that save time later. Use DIVIDE rather than the slash, because it returns blank on a zero denominator instead of an error. Remove filters from the specific column you are slicing by, not from the whole table, or a slicer on another column will stop working. And ALL does the same job as REMOVEFILTERS in this position; Microsoft's own note says that if your tool supports REMOVEFILTERS, use it, because ALL doubles as a table-returning function and is easier to misread.

The variant worth knowing is ALLSELECTED, which removes the filters coming from inside the visual but respects slicers and page filters. Percentages built with REMOVEFILTERS always add to 100 percent of everything; percentages built with ALLSELECTED add to 100 percent of what the user has currently chosen. Both are defensible and they are different reports.

6. Context transition, the other job CALCULATE does

Before the second job: CALCULATE with no filter arguments at all looks pointless. Predict what it could possibly be for, before reading on.

It converts row context into filter context. Microsoft's page is explicit: "Using the CALCULATE function without filters achieves a specific requirement. It transitions row context to filter context."

Row context is what you have when you are standing on one row: inside a calculated column, or inside an iterator like SUMX. It lets you read that row's values. It does not filter anything, which is the fact that surprises people, because it feels like it should.

Here is the demonstration. Add a calculated column to the three-row Products table.

-- calculated column on Products, no CALCULATE
Revenue Wrong = SUM ( Orders[Revenue] )

-- calculated column on Products, with CALCULATE
Revenue Right = CALCULATE ( SUM ( Orders[Revenue] ) )

Without CALCULATE, you are standing on the Desk row and can see the word "Desk", and nothing is filtered, so the sum is the whole table: 9,890 on every row. With CALCULATE, the row you are standing on becomes a filter on Products, that filter travels down the relationship to Orders, and you get 5,060.

The same thing happens inside SUMX, and there is one shortcut worth memorizing: when you reference a model measure inside a row context, the transition is automatic. Microsoft states it plainly: "When you use a model measure in row context, context transition is automatic." That is why SUMX(Products, [Total Revenue]) gives 9,890 and not 29,670. The measure reference is doing an invisible CALCULATE.

Say in one sentence why this is the single most common source of confusion in DAX. It is because the same function does two unrelated-looking jobs, and the second one happens silently every time you use a measure inside an iterator.

7. What a boolean filter argument may not contain

Before the rules: Products[Product] = "Desk" is a boolean filter. Guess what happens if you write Orders[Revenue] > [Average Order] instead, where the right side is a measure.

It errors. Microsoft's page lists the constraints on a boolean filter expression and they are strict:

• It can reference columns from a single table.
• It can't reference measures.
• It can't use a nested CALCULATE function.
• It can't use functions that scan or return a table unless you pass them as arguments to aggregation functions.

Every one of those is fixed the same way: use FILTER, which takes a table and a condition and returns the rows that survive, and which has none of those restrictions.

Above Average Revenue =
VAR AvgOrder = AVERAGE ( Orders[Revenue] )       -- 618.125
RETURN
    CALCULATE (
        [Total Revenue],
        FILTER ( Orders, Orders[Revenue] > AvgOrder )
    )
-- 6,475, across the 8 orders above the average

Two things worth taking from that. The VAR computes the average once, in the outer filter context, before FILTER starts iterating, which is almost always what you meant. And FILTER is a table filter rather than a boolean one, so it replaces the whole table's filters on the columns it touches, which is why FILTER(Orders, ...) over a big fact table is slower than a boolean filter and should be aimed at the smallest table that will do.

The rule of thumb: reach for a boolean filter first because it is faster and clearer, and switch to FILTER only when the condition needs a measure, two tables, or a comparison against something computed.

Edge cases that catch people out

Six that each cost somebody an afternoon.

Two filters on the same column in one CALCULATE. They are combined with AND, so Products[Product] = "Desk", Products[Product] = "Chair" returns blank rather than both. For either-or, write one argument with ||, or use IN { "Desk", "Chair" }.

Filtering the fact table when you meant the dimension. Orders[Product] = "Desk" and Products[Product] = "Desk" give the same answer here and behave differently the moment a product exists in the dimension with no orders. Filter the dimension by default; that is what dimensions are for, as the star schema guide covers.

Expecting blank and zero to behave alike. A measure with no rows returns blank, blank rows are hidden by most visuals, and 0 + BLANK() is 0 while BLANK() + BLANK() is blank. If you need a visible zero, wrap it: [Measure] + 0.

ALL on a table when you meant one column. ALL(Orders) removes every filter on every column of the fact table, so a slicer the user set is silently ignored. Name the column.

A measure that works until somebody adds a slicer. If the measure hard-codes a filter on a column, the slicer on that column stops doing anything, and no error appears. This is the number one cause of "the report is wrong but only for some users".

FILTER over the fact table on a large model. Correct and slow. It iterates every row. Filter the dimension, or use a boolean filter, unless the condition genuinely needs the fact rows.