Make Your Power BI Report Run Faster By Showing The Same Data In Fewer Visuals

Do you have a Power BI report that has a row of card visuals on it, something like this:

Cards

?

It’s a very common thing to do, but if your report is too slow to render you may find that a design like this is part of the problem. Now look at the following report which contains a single matrix that has been configured to look as un-matrix-like as possible but shows the same data:

Matrix

This is likely to perform faster, but why?

Before we go any further, I don’t want you to go and change your reports if you’re not going to get any benefit from doing so. Use Performance Analyzer (as shown here) to determine which visuals on your report are the cause of slow performance – there’s no point redesigning visuals that are fast anyway.

As a general rule the more visuals you put on a report page the slower it’s going to get. It’s logical if you think about it: the more visuals there are, the more queries have to be run against your dataset and the more work Power BI has to do to render the report. I know there is a tendency to try to pack as much information onto a page as possible and this often happens when someone else has designed the report you’re trying to build, but you should always try to resist this. Splitting a single large page into multiple smaller pages, using slicers or filters to reduce the amount of data shown at any one time and avoiding gigantic Excel-like tables are a good idea.

Once you’ve done that you need to see whether you can reduce the number of visuals needed to display the same amount of information: in the example above, the four separate cards show the same data as the single matrix. In part this is because the cards generate four separate DAX queries against the dataset to get the data they need whereas the matrix only generates one DAX query. There is a certain overhead to running a DAX query, so reducing the number of DAX queries needed to get the same amount of data is a good thing. More importantly, in this example Power BI can get the four values required for the single DAX query generated by the matrix much more efficiently than it can in the four separate DAX queries needed by the cards. This is because of something called “DAX Fusion”, which my colleague Phil Seamark blogged about in great detail here, and this can make a signficant difference to performance (Marco Russo’s post on why Analyze In Excel reports may be slower than Power BI reports here also contains some useful information on DAX Fusion).

This is only a simple example; if you want to see a really sophisticated demonstration of replacing several visuals with a matrix I suggest you watch this video by another colleague of mine, Miguel Myers. And of course it doesn’t just apply to cards: any time you can replace multiple visuals with a single visual (remember to be careful using custom visuals though) you’re likely to gain some performance.

11 responses

  1. Great tip. Also worth pointing out that a Matrix/Table with DataBars is far more space efficient than the equivalent number of column charts.

  2. Okay, I understand the logic of your argument; you convinced me. Now how about a brief tutorial on how to create that “single matrix that has been configured to look as un-matrix-like as possible but shows the same data:”

  3. Great info. We have been using the performance analyzer to look at the performance especially that of the KPI cards especially with the okviz card with states.

  4. There was a time I’ve got request from business to create Balanced Scorecard (different KPIs, measures, formatting etc – Excel like). I thought that using “glued” Cards is way to go – oh god I was so wrong.

    At that time I wasn’t thinking it’s problem of queries – in matrix the each field is evaluated as well. I thought that it’s coming from visuals overhead – too many JavaScript objects on a page. Thanks for that article.

  5. Hi Chris,
    Thanks for this! Lately I´m quite keen to make my reports faster and have actually experienced a lot of problems with the matrix visual in Power BI. I read this article from Radacad stating that the matrix visual in fact also fires of separate queries, depending on the amount of cells it needs to retrieve. This doesn’t seem to fit with your findings. I´m really not trying to be a smartypants here but can you explain the difference in these findings? Maybe I´m just misinterpreting…
    Kind regards,
    Guust

    https://radacad.com/why-my-power-bi-matrix-or-table-visual-is-slow

    • Could it be that each visual is separate JaveScript Object and comes with “visual overhead” that takes away CPU/Memory? I don’t know these…

  6. Pingback: Reducing Visual Count to Improve Performance – Curated SQL

  7. Pingback: Power BI Insights: Speeding up reports; User adoption; Merging tables; DirectQuery ERP for Hong Kong SME

  8. Pingback: Power BI Insights: Speeding up reports; User adoption; Merging tables; DirectQuery | MSDynamicsWorld.com

  9. Pingback: What are the options when a Power BI report is slow? - Power BI Performance Guide

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: