article 22285

Performance Implications Of Different Ways Of Fully Expanding A Power BI Matrix Visual

If you have a Power BI report with a matrix visual on it it’s quite likely that you’ll want all the levels in the matrix to be fully expanded by default. But did you know that the way you expand all the levels could have performance implications, especially if you’re using DirectQuery mode? Here’s an example.

I have a DirectQuery semantic model built on top of some of the tables from the SQL Server AdventureWorksDW sample database (apologies for the poor naming):

There are four DAX measures defined on it:

Sales Amount = SUM(FactInternetSales[SalesAmount])

Monday Sales = CALCULATE([Sales Amount], 'DimDate'[EnglishDayNameOfWeek]="Monday")

January Sales = CALCULATE([Sales Amount], 'DimDate'[EnglishMonthName]="January")

Class H Sales = CALCULATE([Sales Amount], 'DimProduct'[Class]="H")

I wrote these measures specifically to exacerbate the problem I’m going to show (by reducing the amount of fusion that is possible) but they are pretty normal, reasonable measures that you might find in any semantic model.

Now let’s say you add a matrix visual to a report page, put these four measures onto the columns axis of the matrix, and drop the CalendarYear column (from the DimDate table), the Color column and the Style column (both from the DimProduct table) onto the rows axis of the matrix. At this point it looks like this:

…but what you want to do now is show all the styles and colours too.

One way to do it – not the most efficient way, but some people like me just love to click – is to expand every year and style individually:

It doesn’t take too long to expand everything and after all you only need to do it once, right? But let’s take the DAX query generated for this visual and paste it into DAX Studio with Server Timings turned on and see what we can see:

There are 14 separate Storage Engine queries – which result in 14 separate SQL queries being sent to SQL Server. The first two Storage Engine/SQL queries get a list of which years and styles have been drilled down on and then there are (4 measures) * (3 levels of granularity) = 12 other Storage Engine queries to get the data shown in the visual. The overall duration of 230ms here is very low but in the real world the SQL queries could be a lot slower, making the DAX query very slow.

The default limits on the number of SQL queries that a DAX query can run in parallel have a big impact on overall performance here as you can see; even though you can increase those limits you may then hit the maximum number of connections that can be opened up to a DirectQuery source, and even though you can increase that limit too if you’re running on a capacity, there are hard limits here. If Power BI needs to open new connections to the data source in order to run these SQL queries, that can also slow things down too because there can sometimes be a noticeable wait when connections are opened. Reducing the number of Storage Engine queries generated by a DAX query is very important when tuning DirectQuery models; the effect is going to be a lot less noticeable on an Import or Direct Lake semantic model but it could still cause problems.

There’s good news though. If you expand the levels in your matrix in a different (and to be honest, much more convenient) way using the “Expand all down one level in the hierarchy” button on the visual header or the “Expand to next level” option on the right-click menu for the rows like so:

…then you get the same result but with a much more efficient DAX query. Here’s what Server Timings shows for the DAX query generated for the fully expanded matrix now:

This time there are only four Storage Engine/SQL queries, one for each measure, and the overall duration is just 50ms. Even though, as you can see from the screenshot, only three Storage Engine/SQL queries can run in parallel and the fourth has to wait for the first query to finish so it can run, that’s less of an issue given the smaller number of queries. I won’t bother showing the DAX for the two versions of the matrix but it’s clear when you look at them the second one is more efficient because it knows it can expand everything on rows rather than just what has been clicked. Of course this type of optimisation is only possible if you are fully expanding your matrix though.