6.3 Row 2 Filter context transition

If you wish to follow along, you can find PBIX/Excel files at the bottom of the article. 


Krešimir Ledinski

Krešimir Ledinski

Microsoft certified expert in the field of Business Intelligence. His biggest passions are DAX, M, and data modeling.

Kristian Radoš

Kristian Radoš

Experienced data analyst. Advanced in SQL, PowerApps and M language.


Context transition is one of the most powerful features of CALCULATE function. Whenever we use CALCULATE inside of a row context (iterative functions like SUMX, FILTER, or in a calculated column), CALCULATE transforms row context to filter context.

What is the difference?

In row context, values from the current row of the iteration can only see their own row, and cannot be used as filters for other rows or tables. When we transform row 2 filter context, we are making all the values of each row become filters that can travel through the model and filter it. This feature is much easier to explain through the following example:

TopQTYWrong = MAXX(VALUES(FactSales[SalesOrderNumber]),SUM(FactSales[OrderQuantity]))
TopQTYCorrect = MAXX(VALUES(FactSales[SalesOrderNumber]),CALCULATE(SUM(FactSales[OrderQuantity])))

We want to calculate the top selling order for each City in the data model. Each order might consist of more lines of products, so we need to iterate through each order, sum its values and then return the order which has the highest OrderQuantity. We will focus on Albany city.

The FactSales table, when filtered to Albany city, looks like this:

We can see that there are 4 orders in this city, each consisting of multiple lines of data. First, let’s explain the wrong calculation which returns the same figure as the [TotalQTY] measure.

No Context Transition

TopQTYWrong =
    VALUES( FactSales[SalesOrderNumber] ),
    SUM( FactSales[OrderQuantity] )

  • MAXX function is adding a virtual X column to the table created with VALUES function, and that table holds 4 SalesOrderNumbers, as shown in the picture below:
  • The X part of the MAXX calculation is the expression SUM( FactSales[OrderQuantity] ).
    The iteration starts for the first row (SO53451). The calculation for the first row is SUM( FactSales[OrderQuantity] ). Since we are inside of a row context of MAXX iteration, and we know that in row context there is no automatic filter propagation or filter context, the only filter applied to the FactSales table is the original filter context one, which is DimCustomer[City]=”Albany”. The fact that this is an iteration of the SalesOrderNumber SO53451 is irrelevant (due to no filter propagation in row context).
  • As a result for the first row of the MAXX iteration, the X part returns value 21, which is the OrderQuantity of all the orders combined. This happens for every row of the MAXX iteration, and for every row, the X part returns the same, total OrderQuantity.

After the X iteration finishes, the MAX simply takes the MAX of the X column, which is 21.
Now let’s check what happens in the correct version of the calculation.

With Context Transition

TopQTYCorrect =
    VALUES( FactSales[SalesOrderNumber] ),
        SUM( FactSales[OrderQuantity] )

The calculation is the same as the wrong one, except for the CALCULATE part.

  1. The 1st part of the calculation is the same, adding the virtual VALUES table with a column consisting of order numbers.
  2. The difference is in the X part of the MAXX function.

Prior to calculation, there is only one filter coming from the filter context, which is DimCustomer[City] =”Albany”. The X iteration starts for the first row in the table (SO53451). For the first row, it needs to perform the X calculation, which has CALCULATE function wrapped around the SUM part. When used in a row context, CALCULATE performs one extremely powerful feature called row 2 filter context transformation.

This means that every value from the current row of the iteration is transformed to a filter, which automatically travels through the model and filters it based on the conditions of that particular row. For the first row to the iteration that means that the value SO53451 becomes a filter for the FactSales[SalesOrderNumber] column, and is added to the original filter context. The evaluation context for the first row of the iteration now consists of 2 filters:

DimCustomer[City] =”Albany”
FactSales[SalesOrderNumber]=” SO53451”

The FactSales table in the first iteration of X looks like this:

After the CALCULATE performed its context transition, the required argument of the CALCULATE function is evaluated (SUM( FactSales[OrderQuantity] )), which returns value 3 for the first row of iteration.

The same transformation happens for every other row of the iteration, and we get correct X values for different sales orders.

After the X iteration finishes, the MAX takes the MAX of the X column, which is now a correct quantity of 8.

Referencing Measures

If we observe the wrong calculation in our previous example, we can see that as an iteration it is using the calculation SUM(FactSales[OrderQuantity]).

TopQTYWrong =
    VALUES( FactSales[SalesOrderNumber] ),
    SUM( FactSales[OrderQuantity] )

If we replace the SUM(FactSales[OrderQuantity]) function with a Measure [TotalQTY], The calculation starts to show correct figures.

TopQTYWrong = MAXX(VALUES(FactSales[SalesOrderNumber]),SUM(FactSales[OrderQuantity]))
TopQTY_Measure = MAXX(VALUES(FactSales[SalesOrderNumber]),[TotalQTY])
TotalQTY = SUM(FactSales[OrderQuantity])

The reason why referencing a measure returns the correct result is the fact that whenever you reference a Measure in other calculations, it always comes encapsulated in CALCULATE. The [TopQTY_Measure] could be read as follows:

TopQTY_Measure = MAXX(VALUES(FactSales[SalesOrderNumber]),CALCULATE([TotalQTY]))

CALCULATE will force the context transition and therefore the measure will return the correct figures.

Returning the Order Number

One thing is to return the quantity of the best order, but what if we need to return the exact order number/s of the best performing order? In that case, we need to start writing a bit more complex DAX code.

In the following example we will start combining multiple DAX concepts to provide the correct answer.

To produce the TopQTYName measure we used the following DAX code:

TopQTYName =
            VALUES( FactSales[SalesOrderNumber] ),
            "TotalQTY", CALCULATE( SUM( FactSales[OrderQuantity] ) )
        [TotalQTY], DESC
    "; "

We will explain the formula through its order of evaluation referencing row numbers in sequence. We will focus only on the cell in a visual having “Albany” filter.

Evaluation Order

Row 6 – VALUES function creates a virtual table of a single column which holds all SalesOrderNumbers of the Albany city. We can represent this table with DAX studio:

Rows 5 & 7 – ADDCOLUMNS function adds a virtual column “TotalQTY” to VALUES table. For each row of the VALUES table, we perform CALCULATE( SUM( FactSales[OrderQuantity] ) calculation that does context transition and sums quantity on the SalesOrderNumber granularity.

Row 3 – TOPN function accepts that virtual table and then returns the top 1 row from it based on the TotalQTY virtual column.

Row 2 – CONCATENATEX takes the result of the TOPN function, then iterates through it and concatenates all SalesOrderNumbers with the highest TotalQTY. Under the “Albany” original filter, there are 2 SalesOrders with the same QTY, and they are both being concatenated and shown on the visual.

SUMMARIZE with Context Transition

If we need to make an iteration over a virtual table that consists of more columns and which needs to accept filter context, the best way would be to use SUMMARIZE.

With SUMMARIZE you can create virtual tables containing multiple columns, then iterate through them while performing context transitions.


We wish to create the best possible content!

If you are a novice looking for a better explanation of any element of the topic, feel free to comment on the part you didn't quite understand!

If you are an expert in DAX and believe certain topic lacks important internals, your comments are more than welcomed!


Notify of
Inline Feedbacks
View all comments


Prefer live training or consultations?

Table of Content

Table of Content


Receive the latest updates on all business analyst news across all platforms.

By subscribing you are agreeing to our Privacy Policy.

Related blog posts


Variables are used in almost every measure you will create. The reason we introduce them last is that they use all other parts of DAX code to produce faster, more powerful and maintainable code. Variables are like containers of a part of the DAX code which can be used throughout...

Read more


Explanation ALLSELECTED is one of the most complex functions in DAX. When used improperly it can lead to unexpected results. As a rule of thumb, you should not use it in iterative functions. It is preferable to use it only as a CALCULATE filter remover, not as a table function....

Read more

6.7 Lineage

What is Lineage? Lineage is a part of DAX mechanics that enables us to use tables as filter arguments for the CALCULATE function. It’s also used in row2filter context transition and other aspects of the data model that involve filter propagation throughout the model. We can state that lineage is...

Read more

6.6 Crossfilter

Crossfilter is a feature of DAX when it filters the underlying dataset even though there aren’t any visual filters present. Introduction In this example, we will explain a very important feature of CALCULATE filter arguments. We will also explain why you should always prefer the combination of ALL/REMOVEFILTER + VALUES...

Read more
Would love your thoughts, please comment.x
Scroll to Top