Virtual tables are tremendously helpful in building and solving unique calculation requests. Most SQL people reading this might find this statement obvious, but for those who only work in DAX, virtual tables can be a bit of a mystery until good applications of their use are shared.
Here’s one particular application worth sharing, and we will solve it two different ways.
THE ASK: A company recently sent out a 7 question survey to Vendors, Employees, and Customers. The survey results were returned and leadership wants to be able to highlight the highest (or lowest) performing questions across the organizations various lines of business (LOB).
Let’s first examine the survey results which are in an unpivoted table of around 50,000 rows. Respondents answer by scoring the question from 1 to 5.
Using a quick average formula
and a matrix table, we can easily show the results for each question and line of business. Since there are 3 audience types receiving the survey – they are added as a slicer at the top of the page.
That’s the easy part.
Now for the second part – to highlight the highest (or lowest) results across all lines of business for the selected audience. Here’s what that ‘should’ look like for the top 4 results when an audience type is selected from the slicer.
So how do we create this in Power BI?
Well we need to create a tool that will identify the top results for the selected audience – and then compare those results to the measure value in each matrix cell.
There are probably 10+ different ways to accomplish this feat in Power BI – but I wanted to highlight a strategy of solving this using virtual DAX tables. These are tables that only exist in the measure – and can be a bit of a mystery to work with (since we never actually ‘see’ them) – so we’ll also explore how to use DAX Studio to help debug this process.
Our virtual table needs to pre-calculate all of the possible scores that will appear in our matrix table above. At minimum it should have 4 columns: The Question, Audience Type, Line of Business, and Average Survey Score.
Here’s what that should look like.
We can build this virtual table using the SUMMARIZE or SUMMARIZECOLUMNS expressions. These two formulas are quite similar – with SUMMARIZE being the older of the two that still requires the use of “Addcolumns” and a table declaration. Both of these expressions yield the exact same result.
Since they both return a table – we are left with two choices for testing if what we expect to be happening is actually happening.
- Create a calculated DAX table in Power BI
- Use DAX Studio (which by default expects a table expression)
Let’s try #2. Using DAX Studio – we simply paste in our target formula (after EVALUATE) – and select Run. The results populate in the bottom of the screen if the formula is working correctly.
Perfect! It’s working.
The next step is to filter our result table to only show the top (or bottom) 4 results of the selected audience.
For this we will use TOPN.
We can add this formula directly into Dax Studio – by simply changing our summary table into a variable. TOPN acts against our Summary Table and returns the highest (or lowest) rows based on the Average Score column. In this case we will return the TOP 4 rows based on the Average Score column.
This formula is working – but notice that it’s returning the top 4 results across all Audience types.
In Power BI – recall that slicers act to physically ‘filter’ the table they act upon. So when we move this formula back over to Power BI – it will only ‘see’ the Audience type that has been selected in the slicer. If more than 1 Audience type is selected, then our summary table may not work as intended, since it is calculating the Average Score for each Audience type explicitly. What if leadership wants to see the top results when the results for all 3 audience types are averaged together?
We can solve this by removing the “Audience” column from the SUMMARIZE formula. Now the summary table will be constructed with the same selected audience members as the slicer selection.
To make the highlighting part of this equation to work – we need to compare each value in the matrix table to the 4 items returned in our TOPN virtual table. If there is a match – we want to highlight the matrix value.
For this we need ROW CONTEXT in our VIRTUAL table. We can accomplish this using SUMX and iterating the average score value to each row in our virtual table. If there is a match – we will return a 1, if there is no match, we will return a 0.
Let’s update our formula to include these two additional pieces:
At this point it will be easier to shift back to our Power BI Desktop file to continue testing. We expect this formula to return a “1” or “0” based on this comparison – so we can simply plug it into the Matrix Visual.
Well – this was unexpected – as everything is returning a 1.
Worse yet – if we use the SUMMARIZECOLUMNS version of the formula – it doesn’t even return a value, and instead errors out. What’s going on here?
Let’s start with why SUMMARIZECOLUMNS stops working here. Well quite simply – SUMMARIZECOLUMNS does not support ROW CONTEXT on the columns being grouped. Feel free to read more about this here or flip to page 408 in the 2nd Edition of the Definitive Guide to DAX. SUMMARIZECOLUMNS doesn’t work with measures, doesn’t support row context, and doesn’t support operations requiring context transition.
I guess we will stick with SUMMARIZE.
Now why is everything returning a 1? This likely has to do with how our virtual SummaryTable is interacting inside the Matrix visual. We can verify this by running a COUNTROWS of the Summary Table in the same Matrix Visual.
This means that our SummaryTable is getting filtered by the Visual down to a single row. What we wanted was for all rows of the summary table to be returned. That means we need to adjust the filter context of our virtual table.
ENTER CALCULATETABLE and ALLSELECTED.
CALCULATETABLE is exactly like CALCULATE – except we use it on a table expression. It allows us to change to filter context operating on a measure. ALLSELECTED “removes context filters from columns and rows in the current query, while retaining all other context filters or explicit filters”
And with that simple adjustment – our Summary Table is now returning all the necessary rows for each matrix cell.
Now let’s swap the SUMX back in, and at quick glance – it seems to be working correctly! (Pictured below is with no audience selected)
To add conditional formatting to the Matrix visual – we simply need to add a rule to color the background when the results of our highlighting formula return a 1.
When we make a selection on the slicer – the highlighting updates dynamically as well.
How can we achieve the same result using RANKX instead of TOPN?
Well for starters – we need to add a RANK column to our virtual table as identified below:
Then we need to filter the virtual rank table to return only the TOP 4 RANK results.
This gives us the same result as the TOPN.
It should come as no surprise that we can go further with these formulas: .
Perhaps adding TOP & BOTTOM highlighting to the same chart?
Using a TOPN/Rank Slicer with values to pick rather than hard-coding a “4” in the formula.
We could go on and on with variations of the same theme – but the premise is the same. We may need to tweak the summary table or the formulas – but now we have to tools for quick testing table measures in Dax Studio – and a slightly better understanding of why we need to use SUMMARIZE (and not SUMMARIZECOLUMNS) for this particular use case.