Filter context and CALCULATE

#Why it matters

Most Power BI candidates can write SUM. Interviews for BI and analyst roles then ask for "revenue for the UK only, even when a slicer picks France" or "share of total", and that is where people stall. Both need CALCULATE and a clear picture of filter context. It is also the largest topic on the PL-300 exam.

#The concept

Every measure is evaluated in a filter context: the set of filters coming from the visual's rows and columns, slicers, page and report filters. The same [Revenue] measure shows different numbers in each cell of a matrix because each cell has a different filter context.

CALCULATE ( expression, filter1, filter2, ... ) evaluates an expression in a modified filter context:

• a filter like Customer[Country] = "United Kingdom" replaces any existing filter on that column

• ALL ( table or column ) removes filters

• KEEPFILTERS ( ... ) intersects with the existing filter instead of replacing it

Filters flow from dimensions to facts along relationships, so filtering Customer[Country] filters the Sales rows.

#Worked example: measures on the Online Retail model

Using the model from Power BI star schema and your first report:

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

Revenue All Countries =
CALCULATE ( [Revenue], ALL ( Customer[Country] ) )

Share of Total =
DIVIDE ( [Revenue], [Revenue All Countries] )

Revenue Excl Postage =
CALCULATE ( [Revenue], NOT Product[StockCode] IN { "POST", "DOT" } )

UK Revenue (respect slicer) =
CALCULATE ( [Revenue], KEEPFILTERS ( Customer[Country] = "United Kingdom" ) )

Put Country on rows of a matrix with [Revenue], [UK Revenue], [Share of Total] and [UK Revenue (respect slicer)]:

UK Revenue ignores the row's country because its filter replaces it. The KEEPFILTERS version intersects "Netherlands" with "United Kingdom", which is empty. Neither is wrong; they answer different questions. Pick the one that matches the question you were asked.

#Common mistakes

• Using FILTER ( Sales, ... ) for simple column filters. CALCULATE ( [Revenue], FILTER ( Sales, Sales[Quantity] > 10 ) ) iterates the whole fact table. Filter on the column: Sales[Quantity] > 10.

• ALL ( Sales ) when you meant one column. Removing every filter on the fact table also removes date and product filters, so "share of total" becomes share of the grand total of everything.

• Dividing with /. Use DIVIDE to return blank instead of an error when the denominator is zero.

#Interview angle

"Explain filter context and row context" is guaranteed in a Power BI interview. Use this lesson's table as your example. Then expect "percentage of parent" or "share of total"; write the ALL version and say which filters it removes.

#Practice task

1. Share of Country Total for products: each product's share of its country's revenue (ALL ( Product )).

2. A measure for revenue from customers with more than 10 invoices.

3. Explain in two sentences why [UK Revenue] shows 7.31M on the Netherlands row.

#Further reading

• Microsoft: CALCULATE function (DAX)

• SQLBI: row context and filter context in DAX