September 18, 2020
Row Context in Power BI
For Power BI version 2.76.5678.782 (December 2019).
Row context is a property of calculated columns and some functions of being aware of rows within calculations. Row context means that the function travels through every row separately. Expression is calculated for each row and then aggregated into final result.
Row context exists in:
- X-functions, for example SUMX(), AVERAGEX()
- Function FILTER()
- Calculated rows
Let’s look at an example using function SUMX, shown in the image below.
Function SUMX() creates a row context in table Sales (#1). It then iterates through table, row by row. In each row it takes the value of fields Price (#2) and Tax (#3) and adds them up. At the end it adds up all the results into one final result.
Row context in calculated columns
Calculated columns also have the property of row context. Expression is calculated for every row and saved to every cell separately. Main difference between X functions and calculated columns is precisely that X functions don’t save intermediate result and calculated columns do. This is the reason we avoid calculated columns, saving intermediate results can occupy a lot of memory and slow down the model.
Row context doesn’t always create filter context
In Filter Context in Power BI article we saw how filter context works. It is important to understand that row context doesn’t always create filter context. Additionally, row context doesn’t flow through connections. Let’s look what this means through example.
We’ll add calculated column to Products table: TotalSales = SUM(Sales[Value]).
Column will add up all the values in Value column in the Sales table. What do we expect as result? Will value for each product differ? The answer is no, the value will be the same for all rows.
The reason is that row context doesn’t always create filter context. Value is indeed calculated for each row separately, but product in Game & Platform row can’t filter Sales table. Table remains unfiltered and result is the same for each row.