# Curious case of context transition in DAX

Its been a quite some time that I haven’t blogged on Power BI DAX. So finally decided to pen down a very important and one of the most misunderstood concept in Power BI DAX called **Context Transition**.

When it comes to row evaluation in Power BI DAX, there are very two important concepts called Row and Filter Context. A thorough understanding of these two concepts immensely helps in designing powerful and scalable DAX queries.

In this article, I won't be discussing or delving into the details of Row and Filter Context. If you're already familiar with these concepts, only then continuing to read this article would be beneficial to understand the context transition mechanism.

### Classification of Functions in Row and Filter Contexts

By default, some DAX functions operate under a row context, while others operate within a filter context.

For example `ADDCOLUMNS` operate under row context while `SUMMARIZECOLUMNS` under filter context. `Calculated columns` inherently use row context while functions like `CALCULATE` and `CALCULATETABLE` use filter context.

Aggregate functions like `SUM,AVG,MIN,MAX` get evaluated under filter context while iterative functions like `SUMX,AVGX,MINX,MAXX` execute under row context so do all operators like `Addition,Division,Multiplication` etc etc.

You can find the complete list here : [https://dax.guide](https://dax.guide)

![Context transition in Power Bi DAX](https://cdn.hashnode.com/res/hashnode/image/upload/v1734722197094/037ef313-12fd-4423-8e17-1f1fa857e284.png align="left")

### Context Transition Definition

A concise definition of context transition in a single sentence can be:

***Context transition is a process through which DAX transforms an active row context into a corresponding filter context when evaluating a measure.***

So , what does this mean ?

It means that if a row is executing under a row context, using expressions/functions that support filter context you can change the context of the current row from row context to filter context i.e transition the current context from row to column.

For example, a row context is “transitioned” into a filter context when an expression is wrapped in a `CALCULATE` function. This enables the calculations to consider current row’s values as filters.

Now there are some functions that support context transition when used with row context. `CALCULATE` and `CALCULATETABLE` and Time-Intelligence Functions support context transition.

You can find the complete list here : [https://dax.guide](https://dax.guide)

![Context transition in Power Bi DAX](https://cdn.hashnode.com/res/hashnode/image/upload/v1734723152863/7c69db23-f8c3-4e50-81b5-a75981c9fa34.png align="left")

I will use the `CALCULATE` function to demonstrate context transition in this article.

### CALCULATE function

As mentioned earlier, a `CALCULATE` function always runs under a filter context and can be used to transition from a row context to a filter context.

Before we move to on how `CALCULATE` performs a context transition lets do a quick recap of the `CALCULATE` syntax and its evaluation.

```xml
CALCULATE(<expression>, <filter1>, <filter2>, ...)
```

* `<expression>`: The expression to be evaluated. It can be any aggregate function or any other measures
    
* `<filter1>, <filter2>, ...`: The filter expression/s applied to the current filter context.
    

It is important to understand the types of filters that affect `CALCULATE` functions.

There are two types of filters, called Implicit and Explicit filters. I like to call them Internal and External filters.

*Explicit filters (External filters) :* These are the filters are external which means these filters are applied externally by user interaction(slicer or visual filters) or by default filter configuration(page or report filters)prior to `CALCULATE` evaluating the row.

*Implicit filters (Internal filters) :* These are the filters that internal and are part of the `CALCULATE` function but can be added or modified by Explicit filters (External filters).For example using `SELECTEDVALUE` you can modify the internal filters.

***Evaluation Order of CALCULATE function***

On surface, this is a two step process

* **Initial Evaluation** : The expression( example aggregate function) is first evaluated within the context of the current filter context. It means that for a given row if there are any external filters(slicer, page level or visual filters) those are applied and the expression is calculated.
    
* **Filter Evaluation** : Once the expression is calculated, the internal filter expression gets involved and the `CALCULATE` function “applies” them.
    

Lets take a very simple example of a Sales table that has three basic columns

![Context transition in Power Bi DAX](https://cdn.hashnode.com/res/hashnode/image/upload/v1734725948846/56ba3fc3-66af-4b4e-bed7-5b5d87a1d237.png align="center")

Here is a snapshot of the existing data

![Context transition in Power Bi DAX](https://cdn.hashnode.com/res/hashnode/image/upload/v1734727054994/55eff716-f58a-4fd8-84b1-477e4b3c496c.png align="left")

The table contains data for Quantity sold for each date with Net price.

I have uploaded the same csv file [here](https://drive.google.com/file/d/13eFiZl-lODd0Cmhq5hKkm1tCmpXDLAtt/view?usp=sharing).

Now, if I want to fetch Net Price and Dates when the Quantity sold was greater than 5, I can do it through the following DAX query

```typescript
EVALUATE
SELECTCOLUMNS (
    Sales,
    "SaleDate", Sales[Order Date],
    "Net Price", Sales[Net Price],
    "SoldQuanity>5", CALCULATE ( SUM ( Sales[Quantity] ), Sales[Quantity] > 5 )
)
```

![Context transition in Power Bi DAX](https://cdn.hashnode.com/res/hashnode/image/upload/v1734728087788/be0b2a0b-44f6-4d2e-b157-8044b15c337e.png align="left")

I could modify the DAX query to hide the blank dates post application of the filter `Sales[Quantity] > 5`

![Context transition in Power Bi DAX](https://cdn.hashnode.com/res/hashnode/image/upload/v1734729180732/5cab0d4b-835b-42d6-9f58-30acb7b1e7f8.png align="left")

#### CALCULATE function Step-by-Step Evaluation Process for the above example:

* The `SUM(Sales[Quantity])` is first evaluated in the current filter context. This might include filters coming from slicers, visual-level filters, or other parts of the model.
    
* `CALCULATE` applies a filter for `Sales[Quantity] > 5` . This modifies the filter context to only include rows where the `Sales[Quantity] > 5`
    
* The `SUM(Sales[Quantity])` is then evaluated with the modified filter context (only where `Sales[Quantity] > 5`)
    
* The intermediate result of `SELECTCOLUMNS` would be a combination of Dates where rows that dont have a Quantity &gt;5 returning BLANK values against Quantity and the Quantity values that are &gt;5
    
* Finally apply the outer filters to discard dates that have BLANK values for the evaluated column `Sales[Quantity] > 5`
    

Incase I want to fetch the Total Quantity and Individual Quantity for a given date but only consider the Quantity where the sold Quantity &gt;5 with the corresponding Net Sales for those quantities, I can modify the DAX query to this

![Context transition in Power Bi DAX](https://cdn.hashnode.com/res/hashnode/image/upload/v1734730827122/2e2428f1-d85b-45de-84c1-f9c3658483eb.png align="left")

Now that we understand the way `CALCULATE` evaluates a given expression, lets check how `CALCULATE` performs context transition.

To test that we would have to create a use case where the `CALCULATE` function is part of a row context and observe how the `CALCULATE` function modifies the row context.

Lets assume that formulae of `[Total Sales]` is a product of `Sales[Quantity] * Sales[Net Price]`

To calculate the total sales for individual day one might consider simply applying the \[Total Sales\] formula in the DAX query, which would return the value for`[Total Sales]`.

Lets find out.

```typescript
EVALUATE
SELECTCOLUMNS (
    Sales,
    "SaleDate", Sales[Order Date],
	"Quantity", Sales[Quantity],
    "Net Price", Sales[Net Price],
    "Total Sales",SUMX(Sales,Sales[Quantity] * Sales[Net Price])
)ORDER BY [SaleDate]
```

![Context transition in Power Bi DAX](https://cdn.hashnode.com/res/hashnode/image/upload/v1734733162190/f7f68294-c0b6-4ef7-a5ee-f0577b41150f.png align="left")

Well, what just happened here ? Those numbers look absolutely absurd.

There is **NO WAY** that the Total Sales on Jan 1st 2017 is 97+ million and apart from that, the same value i.e 97+ million repeats for ALL the rows in the table.

![Context transition in Power Bi DAX](https://cdn.hashnode.com/res/hashnode/image/upload/v1734733309500/01fbbb0c-fbbb-476f-b867-18bae27637e3.png align="left")

We used a `SUMX` function. Recall that `SUMX` is a iterative function and always executes under a row context. So due its inherent behavior `SUMX` is unaware of the filter that exists across every row.

So, the question now is how to make `SUMX` "aware" of the filter ? If your answer is “do it by using a filter context”, then you are absolutely CORRECT. But how ?

Just introduce a `CALCULATE` function that wraps the `SUMX` function.

```typescript
EVALUATE
SELECTCOLUMNS (
    Sales,
    "SaleDate", Sales[Order Date],
	"Quantity", Sales[Quantity],
    "Net Price", Sales[Net Price],
    "Total Sales",CALCULATE(SUMX(Sales,Sales[Quantity] * Sales[Net Price]))
)ORDER BY [SaleDate]
```

![Context transition in Power Bi DAX](https://cdn.hashnode.com/res/hashnode/image/upload/v1734733693296/f7826c1e-7ae5-4df6-b6bf-6cb29724b374.png align="left")

This way we have now successfully “transitioned” the evaluation from row context to a filter context.

Incase you want to get the sum of the Total Sales for each day you can use `SUMMARIZE` function.

**Note** : `SUMMARIZE` function operates in row context.

```typescript
EVALUATE
SUMMARIZE(SELECTCOLUMNS (
    Sales,
    "SaleDate", Sales[Order Date],
	"Quantity", Sales[Quantity],
    "Net Price", Sales[Net Price]  
),[SaleDate],"Total Sales Per Day",CALCULATE(SUMX(Sales,Sales[Quantity] * Sales[Net Price])))
ORDER by [SaleDate]
```

![Context transition in Power Bi DAX](https://cdn.hashnode.com/res/hashnode/image/upload/v1734735355593/8ad3ddd8-4eaf-4c4f-bf3d-3d3bc982891d.png align="left")

Just to double check if the method that we used to “transition” from row context to filter context is indeed correct, you can use the `REMOVEFILTERS` function to discard the filter context and we have the result similar to the one we had using only the `SUMX` function.

```typescript
EVALUATE
SUMMARIZE(SELECTCOLUMNS (
    Sales,
    "SaleDate", Sales[Order Date],
	"Quantity", Sales[Quantity],
    "Net Price", Sales[Net Price]  
),[SaleDate],"Total Sales Per Day",CALCULATE(SUMX(Sales,Sales[Quantity] * Sales[Net Price]),
  REMOVEFILTERS(Sales)))
ORDER by [SaleDate]
```

![Context transition in Power Bi DAX](https://cdn.hashnode.com/res/hashnode/image/upload/v1734735858657/64b6b3da-3f5d-4637-867a-54ffe04590a9.png align="left")

You can also use `ALL` function instead of `REMOVEFILTERS` to discard the filter context , but I would prefer using `REMOVEFILTERS` as it removes “direct” filters applied on columns while `ALL` removes “direct” as well as “indirect” filters. Indirect filters are the filters created through relationships.

Just incase if you missed the link to the sample file I quoted earlier in the article, [here](https://drive.google.com/file/d/13eFiZl-lODd0Cmhq5hKkm1tCmpXDLAtt/view?usp=sharing) it is.

### Conclusion

With this small example I tried to demonstrate the fundamental approach on how context transition operate in Power BI DAX. Context transition occurs when a calculation moves from a row context to a filter context, as we saw when using functions like `CALCULATE`. It is very important that one understands context transition thoroughly to create accurate and efficient DAX measures, especially when you need to control how filters are applied within a calculation. By mastering context transition you can ensure that your measures produce the intended and error free results.

Thanks for reading !!!