560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
|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!|
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.
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 calculated columns is fairly straightforward: select the columns ad filters and then combine them to make rows.
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:
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.
25 Aytoun Street