Row Context — What it is, When is it available, and its Implications
In order to facilitate analysis and visualization in Power BI a data model must first be created. The data model will consist of individual data tables, relationships between the tables, and calculations to aid in the analysis. Calculations typically come in the form of either calculated columns or measures.
A few key differences between calculated columns and measures include:
Calculated Columns:
- Resolves as a scalar value
- Persists in each row of a table
- Increases the size of the data model
Measures:
- Resolves as an aggregate value
- Displays as a data point
- Does not impact the size of the data model
When a DAX expression is evaluated the values the expression can access are limited by the evaluation context. When a calculated column or measure is evaluated there are two fundamental types of evaluation context: (1) Filter Context, and (2) Row Context.
This article is the first of a series focused on the key fundamentals of Power BI. This first article will focus on the concept of row context. Parts 2–4 in the series will focus on iterator functions, filter context, and context transition.
Understanding Row Context
The row context limits a DAX expression during evaluation to only the current row, the reference to each specific row is defined by the row context.
However, it should be noted that all standard aggregation functions (e.g. SUM(), COUNT(), MAXIMUM(), MINIMUM()) will override the default behavior and impose a filter context on the entire table.
Row context exists when:
- Creating calculated columns within a table
- Creating iterators, see Part 2 for more details on iterator functions
Using Excel as an example, multiplying the values in two cells can be achieved with the formula =A2 * B2. The key difference is that when performing calculations in Excel the formula contains a reference to a cell defined by column A and row 2 however, in DAX there is no reference to a cell. Rather a calculation is carried out column by column [A] * [B]. DAX operates on columns and tables and it is the row context which provides the required row information for the calculation to be carried out.
The above DAX multiplication example, [A] * [B], performs the desired calculation row-by-row through the entire table. This row-by-row functionality makes the row context similar to an iterator.
The row context means iterating over the table or for each row the calculation being conducted is limited to just the current row.
See the upcoming Part 2 of the series for more details on iterator functions.
Power BI Iterators: Unleashing the Power of Iteration in Power BI Calculations
Iterator Functions — What they are and What they do
Creating a Calculated Column
As mentioned above, row context is invoked while creating a new calculated column. As an example, a new SalesAmount calculated column will be added to a SalesOrderDetail table. The example data and Power BI file can be found on GitHub.
GitHub – Power BI Key Fundamentals
Power BI key fundamentals example files
SalesAmount =
SalesOrderDetail[OrderQty] * SalesOrderDetail[UnitPrice] * (1 - SalesOrderDetail[UnitPriceDiscount])
While entering the above formula, after starting to type the column names in the formula bar (e.g. OrderQty, UnitPrice, and UnitPriceDiscount) it can be seen that IntelliSense can recognize and reference the corresponding columns in the SalesOrderDetail table (e.g. SalesOrderDetail[OrderQty]).
Within the SalesAmount expression, only the column names are specified, however, since this is creating a calculated column the row context is available. This means that during the evaluation of the expression SalesOrderDetail[OrderQty] does not reference the entire column but rather the OrderQty value for the specific row within the scope of the calculation. This allows the new SalesAmount column to use the correct values when it is calculated row-by-row.
Viewing the new SalesAmount column shows that the values are calculated for each row in the table. This is because row context means to iterate over each row of the table and perform the calculation for the specific row that is in the current scope.
The new calculated column can then be utilized in creating a new measure, TotalSalesAmount
TotalSalesAmount = SUM(SalesOrderDetail[SalesAmount])
This new measure can then be included in visuals to summarize the sales data, for example, visualize the total sales by product color or by product name.
The tables above show that the new TotalSalesAmount measure for each product color/name and the sum of the SalesAmount calculated column are equal. This highlights that the new measure and the default aggregation (i.e. sum) will produce the same values.
Although a default aggregation of SalesAmount can be used to produce the same values as the measure TotalSalesAmount it is best practice to use a measure for aggregation and not use default aggregations of a column.
Limitation
The method of adding a new calculated column is beneficial for aiding in understanding the impacts and use of the row context. However, a limitation to the approach of creating a column similar to SalesAmount is that each column created in a data model table increases the data model size. This is because calculated columns are created and stored as columns in the data model when the data is loaded (i.e. the .pbix file is opened or data is refreshed). Due to this, a column like SalesAmount typically would not be created in the data model, rather a measure can be used which does not increase the size of the overall data model. Measures do not increase the size of the data model because they are calculated in real-time, that is when the measure is used in a visual (table or chart).
A best practice is to use measures whenever possible.
There are certain situations where a measure cannot be used and a calculated column is required. For example, a calculated column is required when the resulting value is to be used as an axis on a visual.
Replacing a Calculated Column with a Measure
Replacing the SalesAmount calculated column with a measure may seem straightforward. Looking at the two formulas below, it is not unreasonable to think that the reference to SalesOrderDetail[SalesAmount] within the TotalSalesAmount measure could simply be replaced with the formula of the SalesAmount calculated column.
SalesAmount = SalesOrderDetail[OrderQty] * SalesOrderDetail[UnitPrice] * (1 - SalesOrderDetail[UnitPriceDiscount])
TotalSalesAmount = SUM(SalesOrderDetail[SalesAmount])
However, if you create a new measure (e.g. TotalSalesAmount2) and start typing OrderQty IntelliSense will not provide it as an option to select as it did previously when creating the calculated column.
The table columns are not available because by default a measure does not have row context. Without the row context Power BI does not know which specific OrderQty value should be referenced. For creating a measure that requires row context an iterator function will have to be used.
An iterator function can retrieve values from other columns in a table based on the row context, then perform an operation on the column values row-by-row, and finally aggregate the results.
Check out Power BI Iterators: Unleashing the Power of Iteration in Power BI Calculations for an exploration of iterators.
Power BI Iterators: Unleashing the Power of Iteration in Power BI Calculations
Iterator Functions — What they are and What they do
Thank you for reading! Stay curious, and until next time, happy learning.
And, remember, as Albert Einstein once said, “Anyone who has never made a mistake has never tried anything new.” So, don’t be afraid of making mistakes, practice makes perfect. Continuously experiment and explore new DAX functions, and challenge yourself with real-world data scenarios.
If this sparked your curiosity, keep that spark alive and check back frequently. Better yet, be sure not to miss a post by subscribing! With each new post comes an opportunity to learn something new.
Ethan Guyant 