Day 7 — CALCULATE — the function everything depends on

If you learn one DAX function properly, learn this one.

What CALCULATE does

Every measure runs inside a filter context — the set of filters currently applied. CALCULATE is the only function that can change it.

=CALCULATE(the expression, filter1, filter2, ...)

Read it as: work out this expression, but with the filters changed like so.

Every measure on this page runs against the model below — a fact table and three dimensions, the same shape you would build in Power BI Desktop.

Several filters are combined with AND — all of them must be true.

CALCULATE replaces, it does not narrow

This is the behaviour that surprises everybody, and the reason CALCULATE is worth a whole day.

Look at the second column The Forced South column says 7,94,800 on every row — including the North row. The visual said "filter to North". CALCULATE said "filter to South". CALCULATE won.

A filter argument on a column replaces any existing filter on that same column. It does not add to it, and it does not intersect with it. If it intersected you would get a blank, because no row is both North and South.

But only on the same column

Here the filter is on Category, and the visual’s filter is on Region — different columns, so both apply. Every row shows that region’s computers and that region’s accessories, and the two add up to that region’s total.

The rule in one line CALCULATE overrides filters on the columns it mentions, and leaves every other filter alone.

ALL — removing filters

ALL is how you escape the current context. It is what makes a percentage-of-total possible.

The middle column ignores the row it is on and always returns the full 30,37,800. Divide one by the other and you get each region’s share — and they add to 100%.

This is the percentage-of-total pattern Numerator: the measure in its normal context. Denominator: the same measure wrapped in CALCULATE with ALL. Learn this shape and you can build it for any dimension.

FILTER — for conditions CALCULATE cannot express

A plain filter argument compares a column to a value. When you need something more involved, use FILTER, which walks a table row by row.

FILTER is slower — use it only when needed CALCULATE(SUM(...), Product[Category]="Computers") and CALCULATE(SUM(...), FILTER(Product, Product[Category]="Computers")) give the same answer, but the first is much faster because the engine can apply it directly. Reach for FILTER when the condition compares two columns, or uses a measure.

FILTER(Table) versus FILTER(ALL(Table))

FILTER(Sales, ...) starts from the rows already visible, so it respects the region. FILTER(ALL(Sales), ...) starts from every row in the table and ignores it. Same function, one word of difference, completely different answer.

Combining it all

Category comes from the visual, North comes from the measure, and the share compares them. This is what a real report measure looks like.

Try these yourself

Write a measure for South region sales only.
Write a measure for Accessories sales only.
Build a % of total by Product[Category] instead of Region.
Explain why CALCULATE(..., Region[Region]="South") shows the same number on every row.
Use FILTER to total only orders with a quantity of 20 or more.

← PreviousDay 6 — The measures you will write every day Next →Day 8 — Filter context and row context