ExamSprintLab

DAX basics: filter context and CALCULATE

Updated October 9, 2026

DAX is the formula language of Power BI, Analysis Services and Fabric semantic models, and nearly everything in it depends on context: the set of rows a formula can see at the moment it runs. Row context means “the current row” and exists in calculated columns and inside iterator functions. Filter context means “the rows that survive the filters on this report cell” and exists for every measure. The decision rule: when a question asks you to change which rows a measure sees, the answer is almost always CALCULATE, because it is the only function whose job is to rewrite filter context.

Evaluate one cell by hand

Take a model with a Sales table (one row per order line, with Amount and ProductKey) related many-to-one to a Product table (Color, Category). A measure is defined once:

Total Sales = SUM ( Sales[Amount] )

A measure has no value of its own. Each cell of a visual runs it again with different filters, and Microsoft’s DAX overview says exactly that: the result of a measure cannot be determined without context. Put Category on the rows of a matrix and Color on the columns. The cell for Bikes and Blue is calculated with two filters active, Product[Category] = Bikes and Product[Color] = Blue. Those filters travel through the relationship and shrink Sales to the matching order lines, and SUM adds what is left. The grand total cell has no filters at all, so it sums everything.

That is the entire mechanism. Everything else in DAX either adds filters, removes filters, or creates a row context.

Row context versus filter context

Row contextFilter context
MeaningOne current rowA filtered set of rows
Created byCalculated columns, iterators such as SUMX and FILTERSlicers, visual axes, report filters, CALCULATE
Filters other tables?No, it does not travel across relationships by itselfYes, along relationships (direction matters)
Typical use[Price] * [Qty] per lineAny aggregate in a measure

The trap sits in the second row. Row context does not filter anything. A calculated column that contains SUM ( Sales[Amount] ) returns the same grand total on every row, because the aggregate still sees the whole table.

CALCULATE in three moves

CALCULATE ( <expression>, <filter1>, <filter2>, ... ) evaluates the expression in a modified filter context. Per Microsoft’s reference, each filter argument behaves in one of two ways: if the column is not already filtered, the new filter is added; if it is, the new filter overwrites the existing one on that column (unless wrapped in KEEPFILTERS).

Blue Sales = CALCULATE ( [Total Sales], Product[Color] = "Blue" )

In the Bikes and Red cell, the existing Red filter on Color is overwritten, so this measure shows Blue sales for Bikes. That surprises people, and exam writers know it.

To compute a percent of total, remove the filter instead of setting one:

% of All Categories =
DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], REMOVEFILTERS ( Product[Category] ) ) )

The numerator keeps the cell’s filters. The denominator runs with the Category filter cleared. ALL, ALLEXCEPT and REMOVEFILTERS are the filter modifiers; Microsoft recommends REMOVEFILTERS where it is supported because ALL also doubles as a table-returning function. ALLSELECTED is the variant that keeps what the user chose in slicers, which is what “percent of the visible total” needs.

The third move is context transition. Inside a row context, calling CALCULATE (or referencing a measure, which wraps it implicitly) converts the current row into equivalent filters. So SUMX ( Product, [Total Sales] ) evaluates the measure once per product, each time filtered to that product.

Measure or calculated column

A calculated column is computed at refresh, stored in the model, and has row context. A measure is computed at query time and has filter context. Ratios belong in measures, because summing a stored per-row ratio produces a wrong total. Rule of thumb: if the value must react to slicers, it is a measure; if it is used to slice or filter, it can be a column.

Which exams test this

ExamHow it appearsOn this site
PL-300Model the data (25–30%): write model calculations in DAX, naming CALCULATE explicitly, plus single aggregation, time intelligence, semi-additive and statistical measures, calculated tables and columns, and calculation groups. Visualize and analyze the data adds visual calculations written in DAXModel the data explained
DP-600Implement and manage semantic models (25–30%): calculations with DAX variables, iterators, table filtering, windowing and information functions, calculation groups, and improving DAX performance. Prepare data also asks for selecting, filtering and aggregating data with DAX queries (outline in force from October 19, 2026)Semantic models explained

How exam questions are built around DAX

Most items give a small model, a requirement in words and four expressions, and ask which one returns the right number. Read the expressions for these decisions:

  • Measure or column. “Must respond to slicers” means measure. “Used as a slicer or axis” means column.
  • Overwrite or keep. A CALCULATE filter on a column the visual already filters replaces that filter. If the stem says to respect the user’s selection, look for KEEPFILTERS, or for ALLSELECTED rather than ALL.
  • Remove or restrict. “Percent of total” needs a filter removed; “only for blue products” needs one added.
  • Iterator or aggregator. SUM ( Sales[Qty] * Sales[Price] ) is invalid, because SUM takes a single column. The row-by-row version is SUMX.
  • Total row. A ratio that is right per row but wrong in the total points to a column holding a ratio, or a measure that is not aggregating at the right grain.

Time intelligence adds one more constraint word: contiguous date table. If the stem mentions gaps in dates or no date table, fix that before choosing a function.

Practice questions

Question 1. A Sales table relates to a Product table. The measure Total Sales sums Sales[Amount]. You need a measure that shows each product category's share of sales across all categories, and the visual has Category on the rows. Which expression is correct?

  • A. DIVIDE ( [Total Sales], [Total Sales] )
  • B. DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], Product[Category] = SELECTEDVALUE ( Product[Category] ) ) )
  • C. DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], REMOVEFILTERS ( Product[Category] ) ) )
  • D. DIVIDE ( [Total Sales], SUM ( Sales[Amount] ) )
Show answer

Answer: C

The numerator keeps the cell’s filter context, and the denominator uses CALCULATE with REMOVEFILTERS on Category to clear that one filter, giving the grand total. Option A divides the value by itself and always returns 1. Option B overwrites the Category filter with the category already selected in that row, so the denominator equals the numerator and every row returns 1. Option D divides by a SUM that is evaluated in the same filter context, so it also returns 1 in every cell.

Question 2. A report shows Total Sales by Color. A measure applies CALCULATE to Total Sales with a filter argument that sets Product[Color] equal to the value Blue. What does the row for Color = Red display?

  • A. Red sales only
  • B. The same value as the Blue row
  • C. Blank
  • D. An error, because Color is already filtered
Show answer

Answer: B

A CALCULATE filter on a column that is already filtered overwrites the existing filter on that column. The Red filter is replaced by Blue, so the Red row shows Blue sales. It is not blank, because the new filter still matches rows. It does not show Red sales, because the Red filter was replaced. It is not an error, because the expression is valid in a measure.

Go deeper