Future-Proof Your Career, Master Data Skills + AI

Blog

Future-Proof Your Career, Master Data Skills + AI
Blog Home

Blog

Explaining Row Context In Power BI

by | 9:00 am EST | December 13, 2019 | Business Intelligence, Data Modeling, DAX, Power BI, Time Intelligence

I have found out that most people get confused in regards to understanding how DAX works in row context. This is because row context can be quite complicated and unnecessarily so. So we’re going to touch on row context throughout this article. You may watch the full video of this tutorial at the bottom of this blog.

The way I think about row context is through iterations or iterating functions. As soon as I made that distinction in my mind, it made more sense in terms of what was actually going on in the calculation engine.

As a recap, we have discussed that there are three types of contexts: the evaluation context, the filter context, and the row context.

Any DAX calculation works in a two-step process. The evaluation context always goes first and then it branches out into either filter context or row context depending on what function you write.

Understanding Row Context

Row context is all about iterating.

I’m going to show you a couple of examples and hopefully this will help in your understanding of what row context actually does in the back end.

Row Context VS Filter Context

In our example, the formula is being calculated via filter context because it is a simple aggregation. It sums up the entire Quantity column after all the initial filters have been put in place.

row context

The initial filter on the Sales table is Product 1. By summing up every quantity that has been sold, we get 165.

row context

Now I’m going to show you how to get the exact same result, but calculated in a very different way. First, I’m going to create a new measure for Quantity Sold Iteration using an iterating formula.

row context

Examples of iterating functions include SUMX, AVERAGEX, MANYX, and COUNTX. In this case, we are going to use SUMX.

The SUMX function has asked me to put in a table, so I’m going to put in the Sales table. And then it asked me for an expression so I just put in the Quantity column.

This function is quite different to the first function, but I get exactly the same result when I drag it in.

row context

We’re getting exactly the same results here because it is ultimately the same calculation. It was just calculated in a different way.

row context

The Quantity Sold Iteration was calculated using row context, while the Total Quantity Sold was calculated using filter context.

Calculating Quantity Sold Iteration Using Row Context

Let’s now go through step by step how the Quantity Sold Iteration is calculated. First, you have to remember to leave the initial valuation context, which is Product 1 in this case. And then via row context, we’re iterating through every single row in the table that we specify.

In this case, we’re specifying the Sales table and then we go and look at every single row in the Quantity column.

row context

During the initial evaluation, Product 1 would be determined by whatever is filtered in the Product ID column. And then it counts up the rows under the Quantity column. Every time it hits a row it goes in and saves the result into memory.

After it reaches the bottom of the table, it then goes and does an evaluation of all the results that are saved into memory, and then it will do whatever calculation you ask of it, which in this case is SUMX.

Why Use A Row Context Or An Iterating Function?

This is because inside an iterating function you can do far more complex things. You can write some very advanced logic inside.

For example, for every row in the Sales table, the quantity is multiplied by 2. You’ll see now that the same calculation process is occurring, but we’re getting different results.

row context

This is because for every single row, we’re writing some different logic.  After going through every single row in the quantity column, we multiply the value by two, and then it gets saved into memory. At the end of that, we do a SUM.

We can also write different things here including advanced logic such as the IF or SWITCH logic.

There are heaps and heaps of examples of where it would be better to use an iterating function or row context to do a calculation rather than an aggregation function or filter context.

Key Notes In Understanding Contexts In Power BI

To recap all the key things about context, remember that calculating in DAX works via a two-step process. The first step is always the evaluation context. It matters so much what the evaluation context is.

Once you understand what the evaluation context is for any individual result, DAX then branches out into two different ways. It can either calculate via filter context or via row context.

Choosing which context to use depends on what formula you are writing. So if you write a simple aggregation formula, it will then go and calculate it via filter context. But if you write an iterating function like SUMX, then it will calculate the formula via row context.

Once you’re getting slightly more advanced in DAX, you can actually have multiple contexts in the same function. But before you get there, you really have to understand what context is in its simplest form.

***** Related Links *****
How the DAX Calculation Engine Works
ALL Function in Power BI – How To Use It With DAX
What Is Context Transition And Why Does It Matter?

Conclusion

Understanding the types of context is absolutely crucial as you learn DAX and want to do more advanced work. When you start writing really advanced DAX formula, you have multiple contexts working at the same time and you need to understand what each individual part of that formula is doing to achieve your desired results.

Here are some recommended links for you to learn more about the application of row context inside of Power BI:

Evaluation Context – (1.8) Ultimate Beginners Guide to DAX

Filter Context – (1.9) Ultimate Beginners Guide to DAX

Enjoy working through this one.

Sam

[youtube https://www.youtube.com/watch?v=A0sBJxCoXx8&w=784&h=441]

Related Posts