Sign in or start a free trial to avail of this feature.
10. Understanding the Row Context
In DAX, we don’t refer to individual rows in our formulas. In this lesson, we’ll see how the row context tells DAX which rows to use in a calculation
- DAX formulas only refer to columns
- A row context is provided when iterating through each row in a dataset
- When performing iterative calculations, such as creating a calculated column, the row context lets DAX know which is the current row
- This allows us to perform a column of calculations with a single formula
You may have noticed that when writing DAX formulas, we have not referred to specific rows in the dataset at any point.
By contrast, in applications like Excel, your formulas generally refer to specific cell or cell ranges, where you specify row and column identifiers. DAX can work with just column references because of the row context.
In this lesson, we'll learn how row context works by creating new columns in a dataset.
Let's start by going to Data View, selecting the Pharma Data Table, and creating a new column called Unit Price.
This column will calculate a unit price for each transaction.
We created this column in the previous course, and saw that it's not analytically useful, as a column does not account for the varying quantity of each transaction.
That being said, it illustrates the concept of row context quite well.
We'll enter the formula sales divided by quantity and press enter.
Note that while both sales and quantity are columns, we expect each row of our new column to be a single number.
The row context provides a current row, which allows the formula to pick out the right value from the Sales and Quantity columns to calculate each row of our Unit Price column. For example, in the first row, Sales is understood to refer to the value of the Sales column in the first row, which is 8.3888.
Likewise, Quantity is understood to refer to the value of the Quantity column in the first row, which is one.
Therefore, a division of columns becomes a division of two numbers, which gives us a result we would expect.
Another illustration of the row context involves using iterator functions.
Iterator functions evaluate an expression, then perform an aggregation of all the values obtained.
Iterator functions exist for most common aggregations and are identified by the letter X.
Common iterator functions include sum X, average X, min X, and max X.
Let's use the average X function to compute the average of these unit price calculations.
Average X iterates throw each row of the dataset, evaluates an expression, and computes the average of the result for each row.
We'll create a new measure, call it average unit price, and enter the average X formula.
We'll compute the average for the Pharma Dataset, and compute the unit price for each row by dividing the Sales by the Quantity.
This formula will iterate through each row of the Pharma Data table.
The unit price calculation is performed for each row, and then the formula will return the average of these calculations.
The row context provides a current row for each calculation, which allows the formula to iterate through the entire dataset, using only a column reference to pick out the correct values.
We'll press enter to create this measure, and we'll go to Report View.
We'll now create a stacked bar chart of average unit price by product.
Remember, this field is not ideal from an analytic point of view, as it does not account for the varying size of different transactions.
The purpose of creating this measure is to simply illustrate the row context.
Although the row context might seem very intuitive, it can get difficult when you have a more complex function. In the next lesson, we'll look at the issues presented by a situation where there are multiple row contexts in a single function.