This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Understanding row context in DAX
Row context (along with its sibling, filter context) is one of the most important concepts in DAX. This blog explains what row context is, and how to use it!

Posted by Sam Lowrie on 29 January 2019

In this blog

Row context for calculated columns
Row context for measures

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:

Dax Calculated Columns Measures Row Context

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 calculated columns is fairly straightforward: select the columns ad filters and then combine them to make rows.

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!

To summarise: row context allows the creation of rows in a column based system and a means of keeping track of what the current row is for a calculation.

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Understanding row context in DAX

Row context for calculated columns

Row context for measures

Head office

London

Manchester