Sign in or start a free trial to avail of this feature.
13. Context Transition in Power BI
The Calculate function is capable of changing a row context into a filter context. We’ll see the effect of this transition, and find out when it occurs in this lesson.
- The CALCULATE function performs its calculation within a particular filter context, but it does not use row contexts
- Therefore, CALCULATE changes any row contexts into a singlerow filter context
- As a result, placing a CALCULATE function around an expression can change the results obtained, even without changing the expression itself
Measures and the Calculate function
- When using a measure in a formula, an invisible CALCULATE function is automatically added around it
- As a result, measures exhibit context transition even if you do not explicitly write a CALCULATE function
Over the past several lessons, we've looked at both the row and filter contexts.
In this lesson, we'll combine these two topics and learn how to transform a row context into a filter context.
Let's go to data view and create a new column in the pharma data table. We'll name the column "Test," and set it to sum the sales field.
This column isn't practically useful, and we won't edit any charts.
Instead, we use it to help illustrate the concept at hand.
The column could return two possible answers. First, it could compute the sum of sales for each row, and therefore, replicate the sales column.
Alternatively, it could calculate the total sales for the entire data set and put the result in every row.
We'll press enter, and see that the second scenario has occurred.
As mentioned in the previous lesson, we can think of this formula as the sum of sales for all rows in the current filter context.
However, in this case there is no filter context.
At every row, there's a row context which is an individual sale, but there is no filter context. This means that at every row, the sum function computes the total sum of all sales in the data set.
Let's put this sum inside a calculate function.
Note that we have no filter condition and the sum is exactly the same.
However, when we accept this new formula, our column shows the total sales for each row.
This has happened because of context transition.
A context transition is a transformation of row contexts into filter contexts, performed by the Calculate function. Calculate does not work with row context. Instead, it converts the row context into a filter context, which filters the data set to single row.
Therefore, the sum function is adding all sales for only a single row, and consequently, we get a column that replicates the sales column.
Let's create a new measure and call it "Total sales." The formula here is simply the sum of the sales column.
We'll press enter to create the measure, select the test column, and replace the formula with our new measure.
We'll press enter again and see that the column is still computing sales for each individual row, despite the fact that the measure doesn't use the Calculate function.
When we reference a measure, DAX automatically places an invisible Calculate function around it.
As a result, we get the context transition of the Calculate function and the column computes the total sales measure for each row individually.
You may now be starting to appreciate why Calculate is set to be the most powerful function in DAX. It can change the filter context, change a row context into a filter context, and can invisibly attach itself to a measure. As such, you should always make sure you understand what the Calculate function is doing and how it's performing.
This concludes our overview of the DAX formula language. Through these last two courses, we've reviewed all the major DAX use cases, as well as some of the key underlying concepts. In the next course, we'll apply many of these concepts and formulas to several practical business examples.