BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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:
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.
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.