Power BI – The Data Model – Row Context

In this lesson we’re going to learn about the concept of row context, which along with filter context, make up a measure or calculated column’s evaluation context –  the overall context in which an calculated column expression or a measure expression is calculated.

So let’s start off with the basics – what is row context?

Row context can be thought of as “the current row” when performing a calculated column or certain measures.  Essentially, what information is in the current row in the columns that I’m using to calculate the calculation that I’m currently performing.

So Why do we need to consider row context? Well we need to consider row context because Power BI does not reference individual cells in its calculations. Instead calculations, both in calculated columns and in measures reference tables and columns instead. Row context needs to exist to allow Power BI to process row by row calculations. When we go through this, again, this is a concept that might seem really obvious but it’s worth doing the example and solidifying it so that there’s no confusion in later lessons.

So, let’s look back at the calculated columns we created several lessons ago, calculating cost from our Gross Margin and price columns.

Open 2018 Retail Analysis file.

Here we are, here’s the 2018 Retail Analysis file and we have here our Cost_GBP column. Now let’s look at the formula we used to create this column. We can get this by selecting the Cost_GBP field in the fields pane.

Here’s the formula. Now as we can see, the formula is made up of Table reference so ‘2018 Retail Analysis Fact Table’ and then the relevant column names which are Price_GBP and GrossMarginAmount. So how does Power BI know what numbers it needs to take from these columns to calculate the cost column over here? If we were in Excel, in ranges there are individual cell coordinates, which tell Excel exactly which cells it needs to use to calculate formula, but here there aren’t.

The answer is row context. When Power BI receives a calculated column instruction, it will go down the calculated column row by row, taking the information from the columns we define in the calculation and that are in the same row that the calculation to calculate the result in each row. This is row context.

To look at it another way, row context actually exists in some parts of Excel, so we’re going to go back into Excel to demonstrate row context in a different way.

Cut to file Row context comparison

So here we are in an excel files and we have two ranges side by side which show the same bit of data – a section of the 2018 Retail Analysis File. On the left hand side we have the data formatted as a properly formatted Excel Table and on the right hand side, we have the same information as an unformatted Excel Range.

So let’s do our cost column again in our Excel Range table. There we go and as we can see, as we scroll down the column, that in the formula bar, the formulas are made up of cell coordinates – not table names or column names.

Now if we do the same calculation in the same way in our Excel formatted table…. There we are, the table column calculates automatically, without the need to copy the formula to all the cells in the column. The reason for this is that there are no cell coordinates in the columns. Here we go, if we scroll down this column, then the formula is the same and this is because Excel is employing row context.

Excel is moving down the column and then in each row it takes the information on that row from the columns in the formula to calculate the number that it returns in the calculated column.

For example in this top row, Excel says ok the formula for this column is Price minus margin so I need to go across to the Price column on this row, find the number then go across to the margin column and find the number and the subtract one from the other and return it in this row. I’ll then go down a row and do the same calculation with the numbers on this row and so on.

This is row context at work and Power BI row context works in the same way.