Context transition is a fundamental mechanism in DAX (Data Analysis Expressions) that transforms row contexts to filter contexts, impacting how calculations are evaluated. While this feature simplifies DAX code authoring, comprehending its functionality is crucial for writing effective measures and DAX calculations. In clear terms, context transition denotes the conversion of a row value into an equivalent column filter. This transformation primarily occurs when the CALCULATE (or CALCULATETABLE) function is executed within a row context. In other words, it involves shifting from individual row-level calculations to applying filters across columns or tables. The resulting context is then used for subsequent calculations or table evaluations.

Most DAX developers find this tricky to understand in the beginning, but a proper understanding of concepts like filter context, row context, and the behaviour of how calculations traverse tables via relationships is essential for mastering the concept of context transition.  I will be elaborating more on the behaviours of these concepts using the data model below. This model includes dimensional tables for customer, product, and date, along with the sales fact table.

Filter context Concept

Filter Context is a set of filters applied to the data before the calculation is carried out. Essentially, it determines which specific subset of data is included or excluded during the evaluation of DAX formulas. These filters are created automatically by Power BI and based on the active conditions at a given moment. As the active filters change dynamically, the calculations adjust accordingly to populate the visuals. You also have an option as a developer to create your custom filter context by leveraging CALCULATE. The following will serve as guides to understanding the behaviour of the filter context on a measure called Total Sales:

1. Create a measure that calculates the Total Sales across the Sales table with no specific filters set within the expression.

Total Sales =
SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )

2. Drag the “Total Sales” measure onto the Power BI canvas and change the visual representation to a Matrix format as shown below:

3. Drag the “Brand” column from the product table, then position it in the visual row and observe how the calculations dynamically adapt to populate the visuals based on the applied filter.

4. Let’s add one more filter called “Year” from the Date table to the visual column, then pay more attention to the “Wide Word Importer” brand’s value across the year.

5. Next, we will establish a custom filter context using the CALCULATE function for the measure of the total sales associated with the “Wide Word Importer” brand. Our objective is to observe how this filter behaves by disregarding any existing filters applied to the visual.
   WWI Total Sales =
   CALCULATE (
       [Total Sales],
       'Product'[Brand] = "Wide World Importers"
   )
   

Row context concept.

In DAX, row context refers to the context in which calculations are performed on a row-by-row basis. This context is particularly relevant when creating calculated columns. When working with calculated columns, each row’s value is computed independently based on the data in that specific row.

However, when creating measures, the situation is different. Measures operate at an aggregate level, and there is no inherent row context. This lack of row context can make it challenging to determine which row to scan or evaluate at any given moment.

To address this, DAX provides iterator functions. These functions introduce an artificial table that can be iterated through, effectively simulating row context for measures. By using iterator functions, we can perform calculations as if there were row context, even though it doesn’t naturally exist for measures.

For instance, let us author a measure that returns the highest sales value per product in each month from our sales table. Now, we aim to aggregate this data to a monthly level to return the highest sales per product per month, and I wish to remind us that the sales transactions were initially recorded daily. Note that this transition in granularity levels may introduce some contextual transitions. The following steps will serve as guide:

1. Position the Total Sales measure on the canvas and change the visual to Matrix.
2. Drag Year and Month from the Date table to the Rows section of the visual.

3. Next, we need to create a measure called “Highest Product Sales”. To achieve this, we’ll utilize the VALUES function to create an artificial table with a single column for unique “ProductKey”. This will enable DAX to iterate through each row value and determine the highest sales using the MAXX

4. Drag the measure to see the highest valued product for each month on the visual that already has filter context of “Year” and “Month”.

5. Now, let's return the product name to the visual by authoring another DAX expression. To display the product names in our visual, we’ll follow these steps to create the measure:
   • Identify the Highest Sales: We’ll use the TOPN function to retrieve the row with the highest sales for the month from the product table.
   • Extract Product Names: Next, we’ll utilize the CONCATENAX function to iterate through the row and extract the name of the product associated with the highest sales.
   • Handling Ties: In case there are multiple products with the same highest sales value, we’ll use the UNICHAR function to arrange their names in a tabular format.

6. Position the new “Highest Selling Product Name” measure on the visual as shown below:

In conclusion, the CALCULATE function exhibits a powerful feature known as context transition. Remember that when CALCULATE is used within a row context, it effectively transitions that row context into a corresponding filter context. This filter context will then propagate through relationships in the data model, moving from the one-side to the many-side. Therefore, it is important to note that when measures are employed within DAX expressions, they are automatically enveloped by a CALCULATE function. Understanding this behaviour is crucial for avoiding unexpected results when working with measures in calculations.