Evaluation Context and Context Transition

Introduction

DAX (Data Analysis Expressions) is not evaluated as a sequence of spreadsheet cell operations. Its result depends on the evaluation context in which an expression is calculated. Evaluation context explains why the same measure can return a different value in a visual, why a calculated column can access values from its current row, and why CALCULATE is central to advanced DAX.

This chapter develops four connected ideas:

1. Row context—the current row being evaluated.
2. Filter context—the set of filters determining which rows are visible.
3. Expanded tables—the conceptual table structures used when filters propagate through relationships.
4. Context transition—the conversion of row context into filter context, principally performed by CALCULATE.

Understanding these concepts enables a practitioner to interpret unexpected totals, diagnose measure behaviour, and write reliable calculated columns and measures.

Learning Objectives

By the end of this chapter, you should be able to:

• distinguish row context from filter context;
• explain how measures and calculated columns are evaluated;
• describe the role of expanded tables and relationships;
• explain context transition and the operation of CALCULATE;
• predict the results of common DAX expressions;
• troubleshoot filters, totals, iterators, and nested calculations;
• apply evaluation-context principles to a business intelligence scenario.

1. The Evaluation Context Model

An evaluation context is the collection of conditions under which DAX evaluates an expression. Context is not a single object visible to the user; it is a conceptual model that helps explain the formula engine’s behaviour.

A DAX expression can be evaluated in:

• a calculated column, where a row context is automatically present;
• a measure, where filter context is supplied by the visual, slicers, filters, and relationships;
• an iterator, such as SUMX, where a row context is created for each row of a table;
• a function such as CALCULATE, which can modify filter context and perform context transition.

Consider a star schema containing Sales, Product, and Date. A report visual displaying Product[Category] creates a different filter context for each category. A measure such as:

Total Sales = SUM ( Sales[SalesAmount] )

does not store one permanent value. It is evaluated separately for each filter context. If the visual row is “Bikes,” the measure sums sales visible for Bikes; if the row is “Accessories,” it sums sales visible for Accessories.

The same measure can therefore produce different values without changing its definition.

2. Row Context

2.1 Meaning of row context

Row context means “the current row.” It is most apparent in calculated columns and iterator functions.

Suppose Sales contains Quantity and UnitPrice. A calculated column can calculate line revenue as follows:

Line Revenue = Sales[Quantity] * Sales[UnitPrice]

For every row, DAX identifies the current Sales row and retrieves the two column values from that row. The expression does not automatically sum the table, nor does it filter other rows. It simply evaluates the current row.

Row context is therefore similar to a cursor positioned on one row, although DAX is not implemented as a conventional procedural cursor.

2.2 Row context does not filter a table

A frequent misconception is that row context itself filters the table for aggregation. It does not. For example:

Sales[Row Total] =
    Sales[Quantity] * CALCULATE ( SUM ( Sales[SalesAmount] ) )

The multiplication uses the current row, but the CALCULATE function is required if the current row is intended to filter the aggregation. Without context transition, a row context does not automatically become a filter context.

This distinction is essential:

2.3 Iterators create row context

Iterator functions end in X, including SUMX, AVERAGEX, COUNTX, and FILTER. They accept a table and evaluate an expression for each row.

Total Revenue =
    SUMX (
        Sales,
        Sales[Quantity] * Sales[UnitPrice]
    )

Conceptually, SUMX performs these operations:

1. obtain the rows of Sales visible in the existing filter context;
2. create a row context for one row;
3. evaluate Quantity × UnitPrice for that row;
4. repeat for all visible rows;
5. add the results.

The iterator does not necessarily create a filter context for each row. It creates row context. Context transition is required when an expression inside the iterator must behave as though the current row were a filter.

3. Filter Context

3.1 Definition

Filter context is the set of filters applied to a DAX expression at evaluation time. It may come from:

• columns placed on rows, columns, legends, or axes of a visual;
• slicers and filter panes;
• report, page, and visual filters;
• relationships between tables;
• explicit filters supplied to CALCULATE or CALCULATETABLE;
• functions such as FILTER, ALL, REMOVEFILTERS, and KEEPFILTERS.

For example, if a visual contains Date[Year] and the current row is 2024, the measure is evaluated under a filter context that includes Date[Year] = 2024. If a slicer selects “North,” the filter context also includes the corresponding region filter.

3.2 Filter context and aggregation

A standard measure respects the current filter context:

Total Sales = SUM ( Sales[SalesAmount] )

If the current context contains 1,000 visible sales rows, only those rows contribute to the sum. If a filter removes all rows, the result is typically BLANK() rather than zero, unless the expression or model causes a different result.

3.3 Changing filter context with CALCULATE

CALCULATE evaluates an expression in a modified filter context.

Sales in 2024 =
    CALCULATE (
        [Total Sales],
        Date[Year] = 2024
    )

The expression [Total Sales] is evaluated after the year filter has been applied. In simplified terms, CALCULATE:

1. receives the current filter context;
2. evaluates or applies its filter arguments;
3. performs context transition if a row context exists;
4. evaluates the first argument in the resulting context.

Filter arguments commonly replace existing filters on the same columns. To add a restriction without replacing an existing filter, KEEPFILTERS may be appropriate:

High Value Sales =
    CALCULATE (
        [Total Sales],
        KEEPFILTERS ( Sales[SalesAmount] > 1000 )
    )

The precise result depends on the existing context and the columns involved. Good DAX design requires knowing whether a filter should replace, intersect with, or remove an existing filter.

4. Expanded Tables and Relationships

4.1 Conceptual meaning

An expanded table is a conceptual representation used to explain how a table includes columns from related tables through many-to-one relationships. The physical model does not duplicate all related columns in memory. Rather, DAX evaluates certain operations as though the table were expanded along active relationships.

In a typical model:

• Sales is the many-side fact table;
• Product is a one-side dimension table;
• Date is another one-side dimension table.

The conceptual expanded Sales table includes columns from Sales and reachable one-side tables such as Product and Date. This helps explain why filtering a dimension can filter the fact table and why functions such as ALL ( Sales ) can have broader effects than a beginner might expect.

4.2 Relationship propagation

Suppose Sales[ProductKey] relates to Product[ProductKey]. A filter on Product[Category] = "Bikes" identifies product keys belonging to Bikes. Through the relationship, only matching rows in Sales remain visible to measures.

The direction and cardinality of relationships matter. In a conventional star schema, filtering normally flows from dimensions to facts. Ambiguous, inactive, many-to-many, or bidirectional relationships can produce results that are difficult to interpret.

4.3 Why expanded tables matter in troubleshooting

Consider:

All Sales =
    CALCULATE ( [Total Sales], ALL ( Sales ) )

A learner may expect this to remove only filters from columns physically stored in Sales. However, the behaviour of expanded tables means that removing filters from the expanded fact table can interact with filters propagated through related dimensions. In practice, when the intention is to remove a filter from a particular dimension, it is usually clearer to write:

All Products Sales =
    CALCULATE ( [Total Sales], REMOVEFILTERS ( Product ) )

This communicates the business intent directly. REMOVEFILTERS is often preferred for clarity when the objective is simply to clear filters, whereas ALL can also return a table and has several uses.

5. Context Transition

5.1 Definition

Context transition is the conversion of the current row context into an equivalent filter context. It is performed by CALCULATE and CALCULATETABLE when they are evaluated inside an existing row context.

Suppose an iterator is scanning Product:

Product Sales Total =
    SUMX (
        Product,
        CALCULATE ( [Total Sales] )
    )

For each product row, CALCULATE transforms the current product row into filters on the relevant product columns. [Total Sales] is then evaluated for that product. SUMX adds the product-level results.

Without CALCULATE, the measure would not automatically receive the current product row as a filter merely because SUMX created row context.

5.2 Context transition in calculated columns

A calculated column already has row context. Therefore, this expression:

Customer Sales =
    CALCULATE ( [Total Sales] )

can transition the current row of a table into filter context. If the calculated column belongs to Customer, the current customer row can filter related sales, provided the relationship and filter direction permit that propagation.

This pattern can be useful, but it should be used carefully. A calculated column is materialized during refresh and increases model size. A measure is generally preferable when the result should respond dynamically to report filters.

5.3 Context transition and measure references

A measure reference includes an implicit CALCULATE-like behaviour when evaluated in row context. This is one reason the following expressions can behave differently:

SUMX ( Product, [Total Sales] )

and

SUMX ( Product, SUM ( Sales[SalesAmount] ) )

The first uses a measure reference, which is evaluated in the iterator’s row context with context transition. The second directly evaluates an aggregation expression; it does not automatically convert the row context into a filter context. If row-by-row filtering is required, use:

SUMX (
    Product,
    CALCULATE ( SUM ( Sales[SalesAmount] ) )
)

This distinction is a frequent source of incorrect totals.

6. A Worked Example: Why Totals Can Surprise You

Assume the following simplified sales data:

The total sales amount is 75.

A measure can calculate revenue from quantities and prices:

Revenue =
    SUMX (
        Sales,
        Sales[Quantity] * Sales[UnitPrice]
    )

The iterator creates a row context for each row and calculates 20, 30, and 25. The result is 75.

Now suppose we want to sum a measure evaluated once for each product:

Revenue by Product =
    SUMX (
        VALUES ( Product[Product] ),
        [Revenue]
    )

VALUES(Product[Product]) returns A and B under the current filter context. The measure reference [Revenue] is evaluated for A and then B. Context transition applies the current product row as a filter. Product A contributes 50; Product B contributes 25; the result is 75.

However, a common incorrect pattern is to use an expression that does not transition context when transition is required. The result may evaluate the same overall total repeatedly—once for each product—and then overstate the result. When a total is unexpectedly multiplied by the number of groups, inspect whether an iterator is repeatedly evaluating an unfiltered aggregation.

7. Nested Contexts and Variable Evaluation

DAX expressions can contain nested row and filter contexts. Variables improve readability and often improve performance, but their evaluation context must be understood.

Sales Variance =
VAR CurrentSales = [Total Sales]
VAR TargetSales = [Sales Target]
RETURN
    CurrentSales - TargetSales

Variables are evaluated in the context where they are defined. They do not behave like formulas that are automatically recalculated in every later context. Consequently, moving a variable definition into or out of an iterator can change the result.

For example:

Wrong Pattern =
VAR Total = [Total Sales]
RETURN
    SUMX ( Product, Total )

Total is evaluated once before the iterator. The same value is then returned for every product row. By contrast:

Product Total =
    SUMX (
        Product,
        VAR CurrentProductSales = [Total Sales]
        RETURN CurrentProductSales
    )

The measure is evaluated within each iteration, where context transition associated with the measure reference can apply.

8. Diagnostic Method for DAX Problems

When a result is unexpected, use a structured process.

Step 1: Identify the evaluation object

Ask whether the expression is a measure, calculated column, calculated table, or iterator expression. Measures normally begin with filter context; calculated columns normally begin with row context.

Step 2: List the active filters

Record filters from the visual, slicers, page, report, and relationships. Determine whether the relevant dimension filters the fact table through an active relationship.

Step 3: Mark every row context

Look for calculated columns and iterator functions such as SUMX, FILTER, and ADDCOLUMNS. Each may create a current-row environment.

Step 4: Locate context transition

Identify CALCULATE, CALCULATETABLE, and measure references evaluated inside row context. Ask exactly which current row becomes a filter.

Step 5: Inspect filter replacement and intersection

Determine whether CALCULATE arguments replace existing filters or should be wrapped in KEEPFILTERS. Check whether ALL or REMOVEFILTERS removes a filter that was needed.

Step 6: Test at different granularities

Place the measure beside dimensions such as year, category, and product. A measure that works at product level but fails at category or grand-total level may rely on an assumption that is true only at one granularity.

Useful diagnostic measures include:

Visible Products = COUNTROWS ( VALUES ( Product[ProductKey] ) )
Visible Sales Rows = COUNTROWS ( Sales )
Current Year = SELECTEDVALUE ( Date[Year], "Multiple or none" )

These expressions reveal the current filter context rather than merely showing the final business metric.

9. Applied Case: Retail Performance Dashboard

Background

This illustrative case concerns Northstar Retail, which sells products through stores and an online channel. Its Power BI model contains Sales, Product, Date, Store, and Customer tables. Management reports that a “sales per product” measure is correct at product level but produces an excessive grand total.

Problem

The analyst writes:

Sales per Product Total =
    SUMX (
        VALUES ( Product[ProductName] ),
        SUM ( Sales[SalesAmount] )
    )

At first glance, the expression appears to calculate sales for each product and add them. However, SUM(Sales[SalesAmount]) is not explicitly evaluated under a filter created from the current product row. The iterator’s row context does not by itself filter the aggregation.

Analysis

The measure is evaluated in a product row context created by SUMX. The direct SUM expression does not necessarily receive that row as filter context. Therefore, the same broader sales total may be returned in multiple iterations.

Solution

Use a measure reference or explicit context transition:

Total Sales = SUM ( Sales[SalesAmount] )

Sales per Product Total =
    SUMX (
        VALUES ( Product[ProductName] ),
        [Total Sales]
    )

Alternatively:

Sales per Product Total =
    SUMX (
        VALUES ( Product[ProductName] ),
        CALCULATE ( SUM ( Sales[SalesAmount] ) )
    )

The current product is converted into a filter, sales are calculated for that product, and the product results are added.

Outcome

The product rows and grand total now reconcile with the underlying transaction data. The analyst also documents the active relationships and tests the result under year, channel, and store filters.

Learning Points

• A visual’s row labels contribute filter context.
• An iterator creates row context but does not automatically filter every aggregation.
• A measure reference or CALCULATE can perform context transition.
• Correct totals require testing at both detail and aggregate levels.
• Model relationships are part of the interpretation, not merely diagram features.

10. Limitations, Governance, and Best Practices

Evaluation context cannot compensate for a poorly designed model. Ambiguous relationships, duplicate keys, inappropriate bidirectional filtering, and mixed-grain fact tables can create results that are technically consistent but analytically misleading.

Recommended practices include:

• use a star schema with clear dimension-to-fact relationships;
• prefer explicit measures over repeated implicit aggregations;
• use descriptive measure names and variables;
• use REMOVEFILTERS when the intention is clearly to remove filters;
• use KEEPFILTERS when a new condition must intersect with existing filters;
• avoid unnecessary calculated columns for dynamic reporting logic;
• test measures at detail, subtotal, and grand-total levels;
• document inactive relationships and special filter directions;
• validate results against independent totals or source-system reports;
• restrict sensitive data through appropriate model security, including row-level security.

DAX results may expose commercially sensitive information if filters or security roles are incorrectly configured. Context transition does not override row-level security, but a poorly understood model can make users believe that a result is broader or narrower than it actually is. Governance therefore includes both formula correctness and controlled access to data.

Contemporary Practice and AI-Assisted Development

Modern Power BI tools can generate DAX suggestions and explain formulas. Such assistance can accelerate development, but it does not reliably infer business grain, relationship intent, or security requirements. An AI-generated expression should be checked by tracing its row context, filter context, context transition, and relationship propagation. Validation against known totals remains necessary.

Chapter Summary

Evaluation context is the foundation of reliable DAX interpretation. Row context identifies a current row and is created in calculated columns and iterators. Filter context defines which rows are visible to an expression and is supplied by report selections, relationships, and filtering functions. Row context does not automatically filter aggregations.

Expanded tables provide a conceptual explanation of how relationships influence filter behaviour, particularly when filters are removed or propagated through a model. CALCULATE modifies filter context and performs context transition when row context exists. This makes it possible to evaluate a measure for each row of an iterator or calculated column.

Most troubleshooting begins by identifying the evaluation object, listing active filters, locating row contexts, finding context-transition operations, and testing the expression at multiple granularities. Reliable DAX depends on both correct formulas and a well-designed, governed data model.

Key Terms

Evaluation context: The conditions under which a DAX expression is evaluated.

Row context: The current row available to a calculated column or iterator expression.

Filter context: The set of filters restricting rows visible to an expression.

Iterator: A function that evaluates an expression row by row, commonly identified by an X suffix.

Measure: A DAX calculation evaluated dynamically in the current filter context.

Calculated column: A column computed during model refresh for each table row.

Context transition: Conversion of row context into filter context.

CALCULATE: A function that evaluates an expression in a modified filter context.

CALCULATETABLE: A function that evaluates a table expression in a modified filter context.

Expanded table: A conceptual table including columns reachable through relationships.

Relationship propagation: The movement of filters between related tables.

Star schema: A model with a central fact table surrounded by dimension tables.

SUMX: An iterator that sums an expression evaluated for each row.

VALUES: A function returning the distinct visible values of a column or table.

FILTER: A function that returns rows satisfying a Boolean condition.

REMOVEFILTERS: A function used to clear filters from specified tables or columns.

KEEPFILTERS: A function that makes a filter intersect with an existing filter rather than broadly replacing it.

Granularity: The level of detail represented by a row or calculation.

Row-level security: Rules restricting the rows visible to particular users or roles.

Review Questions

Short Answer Questions

1. What is row context, and where is it commonly created?
2. How does filter context affect a measure?
3. Why does an iterator not automatically turn row context into filter context?
4. What is context transition?
5. Why are expanded tables useful as a conceptual model?

Long Answer Questions

1. Compare row context and filter context, using one DAX example for each.
2. Explain the sequence of operations performed by CALCULATE.
3. Describe how relationships and expanded tables influence a measure’s result.
4. Explain why a measure reference inside SUMX can behave differently from a direct aggregation.
5. Develop a systematic method for troubleshooting an incorrect grand total in Power BI.

Analytical / Application Questions

1. A measure returns the same total for every product and an overstated grand total. Identify the likely context error and propose a corrected DAX pattern.
2. A report’s year slicer changes sales figures, but a calculated column does not change after the slicer is used. Explain why and recommend whether a measure would be more appropriate.
3. A model uses bidirectional relationships and ALL(Sales) produces confusing results. Analyse how expanded tables and filter propagation may contribute, and recommend modelling and DAX improvements.

Multiple Choice Questions with Answers

Question 1

Which context identifies the current row during evaluation?

A. Filter context

B. Row context

C. Security context

D. Relationship context

Correct Answer: B. Row context

Question 2

Which function performs context transition when evaluated inside row context?

A. SUM

B. VALUES

C. CALCULATE

D. DISTINCTCOUNT

Correct Answer: C. CALCULATE

Question 3

What does a visual row containing Product[Category] normally contribute?

A. Row context only

B. Filter context

C. A calculated column

D. A new relationship

Correct Answer: B. Filter context

Question 4

Why is SUMX called an iterator?

A. It removes all filters before summing

B. It evaluates an expression for each row and aggregates the results

C. It creates a physical copy of the table

D. It changes relationship cardinality

Correct Answer: B. It evaluates an expression for each row and aggregates the results

Question 5

Which expression explicitly enables context transition for a direct aggregation inside SUMX?

A. SUMX(Product, SUM(Sales[SalesAmount]))

B. SUMX(Product, VALUES(Sales[SalesAmount]))

C. SUMX(Product, CALCULATE(SUM(Sales[SalesAmount])))

D. SUMX(Product, REMOVEFILTERS(Sales))

Correct Answer: C. SUMX(Product, CALCULATE(SUM(Sales[SalesAmount])))

Question 6

What is the primary purpose of filter context?

A. To identify the current physical row only

B. To restrict the rows visible to an expression

C. To create a new table relationship

D. To materialize a calculated column

Correct Answer: B. To restrict the rows visible to an expression

Question 7

Which function is generally clearest when the intention is to remove filters?

A. REMOVEFILTERS

B. SUMX

C. RELATED

D. COUNTROWS

Correct Answer: A. REMOVEFILTERS

Question 8

What can happen if an unfiltered total is repeatedly evaluated inside an iterator?

A. The result may be overstated

B. All relationships are deleted

C. The model becomes a calculated table

D. The result must always be BLANK

Correct Answer: A. The result may be overstated

Question 9

In a conventional star schema, filters usually propagate from which tables to which tables?

A. Fact tables to dimensions

B. Dimensions to facts

C. Measures to dimensions

D. Calculated columns to slicers

Correct Answer: B. Dimensions to facts

Question 10

Where are calculated columns normally computed?

A. Dynamically for every visual interaction

B. During model refresh, using row context

C. Only when a slicer is selected

D. Only after context transition in a measure

Correct Answer: B. During model refresh, using row context