Understanding row context in DAX

Row context is often the answer to questions found about DAX - but what is it? This blog will explain what row context is and does, firstly in relation to calculated columns and then in relation to measures.

Row context for calculated columns

Consider this sales information:

For each sale, there is a unique SaleId with the Quantity of items bought and the Price of each item.

To calculate the Sale Total the number of items sold (Quantity) should be multiplied by the cost of each item (Price), in a new calculated column:

Remember this is for calculated columns - we will come to measures in a bit.

This would all appear to make perfect sense and indeed it does work (a correct value is returned for each sale):

This owl is no Rachel Riley but each row's Price X Quantity does (after a quick calculator check) equate to the Sale Total.

So what is the problem, you ask? Rows. Or more specifically the fact that they don't exist! Let's consider how the BI storage system (VertiPaq) actually stores data:

A row based system (top) saves and accesses data as rows, so that for the calculation above it would access 3 unnecessary columns. A column-based  system such as DAX uses (bottom) only accesses the required information.

After any filtering has occurred to remove unwanted data, row context stitches the columns together effectively creating rows. Now it is possible to perform a calculation with interaction between multiple columns.

Row context for measures

Measures aren't attached to a table and so by default won't have any columns or rows to interact with. So how does a measure work with rows?

By default, it doesn't. There is no row context and so Power BI produces an error.

Instead we can add row context using an iterator function like SUMX. Iterators (and calculated columns) perform a calculation for each row in a given table:

Most functions which require a table argument (which for this example is the Sales table) are iterators. For each row in Sales the Price and Quantity columns are multiplied and then the results added up.

When building more complex DAX expressions multiple row contexts may exist. Consider this example of calculating the Sale Total for small transactions (ones having less than 5 items bought).  For this example, first the FILTER function iterates over rows:

FILTER creates a row context within the Sales table and row by row checks how many items were sold, filtering out any that don't have 4 or less sales.

Next the SUMX function creates a row context within the filtered version of the Sales table, working out for each row the Price * Quantity. If you find it hard to visualise, try using variables:

Var creates a variable with the given name. A variable can have its value set and its contents referenced. Use Return to tell Power BI which the value to return from a measure.

Now it is easier to see how that:

• the  FilterTable variable uses row context to check each row in the Sales table and remove any rows which have a Quantity of more than 5; then
• the SumCalc variable uses the row context in the FilterTable to multiply the Quantity by the Price.

And that's row context!