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 01 February 2019

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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:

Row context DAX

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):

Row Context

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:

VertiPaq

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?

DAX Row context

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:

Row Context

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:

Row context

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:

Variables Row context

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.

This blog has 0 threads Add post