Module 3 — DAX & Filter Context

The most important DAX skill for PL-300 is understanding the context in which an expression is evaluated. Most confusing DAX questions become manageable once you identify the current filter context, whether row context exists, and whether the formula changes either one.

Filter context

Filter context is the set of filters that determines which rows are visible to a calculation. It can come from slicers, visual axes, page/report filters, relationships, drillthrough context, or DAX itself. A measure is always evaluated in the filter context of the cell or visual where it appears.

Row context

Row context means “the current row.” It exists naturally in calculated columns and is created by iterator functions such as SUMX, AVERAGEX and FILTER. Row context by itself does not automatically filter related tables in the same way that filter context does.

What CALCULATE does

CALCULATE() evaluates an expression after modifying filter context. Its first argument is the expression to evaluate, usually a measure; the remaining arguments add, replace or modify filters.

CALCULATE() also performs context transition when it is evaluated in row context. Context transition converts the current row into equivalent filter context. This is why a measure evaluated inside an iterator can return a result for the current entity instead of the whole table.

On the exam, avoid the misconception that CALCULATE() is a generic “make filters work” function. Measures already respect filter context. Use CALCULATE() when you need to change that context or trigger context transition.

Many analytical measures work by comparing the current filter context with a deliberately modified version of that context. DAX filter-modifier functions let you remove, preserve or combine filters with precision.

REMOVEFILTERS and ALL

REMOVEFILTERS() is the clearest choice when your intention is simply to clear filters. You can remove a filter from one column, several columns or an entire table. If you remove only Product[Category], filters on Date, Region, Customer and other columns remain in effect.

ALL() can also clear filters when used inside CALCULATE(), but it additionally acts as a table function that can return all rows or values. For modern readable DAX, REMOVEFILTERS() often communicates the filter-removal intention more directly.

ALLEXCEPT

ALLEXCEPT(Table, Column...) removes filters from the specified table except the columns you name. For example, it can clear all Customer-table filters while preserving Customer Segment. It does not mean “remove every filter in the whole report except this one column”; unrelated tables can still filter the calculation.

KEEPFILTERS and replacement behavior

A Boolean filter argument in CALCULATE() normally replaces an existing filter on the same column. KEEPFILTERS() changes that behavior so the new filter is intersected with the existing one.

These functions are central to percent-of-total, benchmark, market-share and “ignore one slicer but keep everything else” calculations. The safest approach is to remove the smallest necessary filter rather than clearing the entire model context.

DAX has two broad styles of aggregation: functions that aggregate an existing column directly, and iterator functions that evaluate an expression row by row over a table.

SUM(Sales[Amount]) simply adds the values already stored in one column. By contrast, SUMX(Sales, Sales[Quantity] * Sales[UnitPrice]) first creates row context over Sales, evaluates the multiplication for each row, and then sums those row results.

Common iterators

• SUMX(Table, Expression) evaluates an expression for each row and sums the results.
• AVERAGEX evaluates an expression for each row and averages the results.
• MINX and MAXX evaluate an expression row by row and return the minimum or maximum result.
• FILTER(Table, Condition) iterates a table and returns only rows where the condition is true. Because it returns a table, it is often used as a filter argument to other DAX functions.

VALUES and changing the grain of a calculation

VALUES(Column) returns the distinct values visible in the current filter context. It is frequently paired with iterators to calculate at an entity grain that differs from the underlying fact table.

When a measure such as [Total Sales] is referenced inside an iterator, its evaluation uses context transition so the current iterator row can become filter context. This behavior is what makes entity-level patterns such as average sales per customer practical.

Variables make DAX easier to read, debug and maintain by giving names to intermediate results. They also matter conceptually because a scalar variable is evaluated once in the context where it is defined.

A measure can define one or more variables with VAR and then return the final expression with RETURN. Variables are useful when the same expression would otherwise be repeated, when a calculation has several logical stages, or when you want to inspect intermediate values during debugging.

Evaluation behavior

Once a scalar variable has been evaluated, referencing that variable later does not cause the original expression to be recalculated under a new filter context. This is a subtle but important DAX rule.

Safe division

DIVIDE(Numerator, Denominator [, AlternateResult]) is generally preferred to the / operator when the denominator can be zero or blank. It returns blank by default—or an optional alternate result—instead of forcing you to write a separate defensive IF test.

Variables do not automatically make every measure faster, but avoiding repeated evaluation of expensive identical expressions can help, and the improvement in clarity is often valuable on its own.

Time-intelligence functions modify the current date context so the same base measure can be evaluated for another period—such as prior year, previous month or year to date. The key idea is that the date set changes; the business measure usually stays the same.

A reliable time-intelligence model should use a proper Date table that covers the required range and is related to the relevant fact date. When the Date table is filtered by a visual, functions such as SAMEPERIODLASTYEAR() and DATEADD() return a different set of dates, and CALCULATE() reevaluates the measure using that shifted date set.

Common patterns

• SAMEPERIODLASTYEAR('Date'[Date]) returns the corresponding date period one year earlier. It is suited to same-period prior-year comparisons.
• DATEADD('Date'[Date], -1, MONTH) shifts the current date set by a specified interval. It is more flexible when the offset is not simply one year.
• TOTALYTD([Measure], 'Date'[Date]) evaluates a measure from the beginning of the year through the latest date in the current context. Other dimension filters—such as Product or Region—remain unless explicitly removed.

A typical YoY percentage is (Current - Prior) / Prior. The prior-period value belongs in the denominator because the percentage describes change relative to the earlier baseline.

When a fact table contains multiple date roles, remember that time intelligence follows whichever relationship is active for the calculation. Use USERELATIONSHIP() or role-playing date dimensions when the business meaning requires a different date.

Not every measure can be summed meaningfully across every dimension. Semi-additive measures are additive across some dimensions but require special treatment across others—most commonly time.

Balances, inventory levels and headcount snapshots are classic examples. It is reasonable to sum the balances of many accounts on the same date, but summing Monday's balance + Tuesday's balance + Wednesday's balance does not produce a meaningful “weekly balance.” Across time, you normally want the opening, closing, minimum, maximum or average snapshot instead.

Closing-balance reasoning

A closing-balance measure should identify the last applicable date in the current reporting period and return the balance for that snapshot. In sparse datasets, the last date in the Date table can differ from the last date that actually has fact data, so robust logic may need to locate the latest data-bearing date.

Calculation groups

A calculation group lets one reusable transformation apply to many measures. Instead of creating separate measures such as Sales YTD, Profit YTD, Quantity YTD, Sales PY, Profit PY and so on, you can define calculation items such as Current, YTD and Prior Year and apply them to whichever measure is currently selected.

SELECTEDMEASURE() represents the measure being transformed by the calculation item. Calculation groups reduce measure proliferation and centralize recurring logic, but they add semantic-model complexity and are unnecessary when only one isolated measure needs a special calculation.

DAX query view

DAX query view is an authoring and inspection surface where you can run DAX queries against the model. The keyword EVALUATE returns a table expression, while functions such as SUMMARIZECOLUMNS() can group and project model results. This is different from defining a model measure: a DAX query returns a result set for inspection or analysis rather than creating a reusable report object.