Why is Power BI so slow?
Reasons why your Power BI Dashboard might be slow and ways how to fix it.
Have you ever built a Power BI dashboard and after applying some filters, it takes a very long time to refresh and it feels like overall Power BI Desktop is lagging? If that is the case, then follow along this email as that is the topic we will discuss here.
We will be discussing the reasons which you might encounter the most that will cause slowness when creating dashboards as well as offer alternative solutions on how to overcome those challenges.
Reasons why Power BI might be slow and how to fix that
Power BI is a very powerful tool for visualizing your data which in turn will make your life easier (especially if you are a person who likes to see visuals as a way to remember your numbers). If not used properly or if overloaded with elements, your Power BI Dashboard will be very slow. Every time I build a dashboard I make sure I go over the steps listed below and try to come up with ways to eliminate those problems.
You are using way too many visuals within one page
Using too many visuals will cause a slowness in your dashboard. The reason for the slowness is because all those charts are dynamically connected to each other and any interaction with the dashboard, it will automatically require an update on all visuals. Besides the slowness, you should always keep in mind that if a dashboard is overcrowded with visuals, then every visual will seek attention which will me make the user experience even worse. I would highly suggest reading about UI/UX as user interface plays an important role in dashboard usage.
Solution
Limit the number of visuals of up to 3 visuals per page. If you consider you will need more, then create additional pages so the data is loaded only after you open the other page. Personally, I try to use a very simple structure:
2-3 KPIs - A section for key performance indicators where users will be informed about the general performance (in my case Total Sales, Total COGS and Total Profits)
1 General table - Show a table with the main information listed there, if needed you can apply hierarchy levels for drill down-possibilities but keep it simple. (in my case Summary by Product Type)
2-3 visuals - focus only on key information, probably a visual with timing series analysis and other key groupings you would consider important to be shown( in my case Profit % By Segment, Profit % by Product Type and Sales vs COGS Monthly)
2-3 filters - keep your dashboard clean, limit the numbers of filters/slicers you show on your page( in my case Years and Regions).
Use navigation menu for additional slicers/filters - in this one you can add all other filters/slicers which are not as crucial to be in the front page (see navigation menu image below).
If you want to get this Power BI Dashboard, visit my other article here.
There are too many visual interactions in your dashboard
Limit the number of interactions between visuals, unless it is necessary. The reason for that is because every time you filter for a specific visual, if allowed to interact with other visuals, it will update all other visuals based on the filter you are applying which requires a recalculation for every calculated measure used. Let’s take as an example our dashboard above. You want to show main KPIs as static, meaning that they do not have to change if you are interacting with other filters/visuals. In order to do that, after you have selected the visual you do not want to interact with, you go to Format menu and then click Edit Interactions. Then click on the highlighted icon to turn off the interaction (see below).
You are using many-to-many relationship between your tables
Relationships between different tables play an important role in the dashboard and if not used properly, they might cause the slowness of your dashboard. The overall suggestion is to limit yourself on using one-to-many and many-to-one relationships. One-to-one means one of the data tables is redundant and can get combined within one table while many-to-many relationships (although it is possible and it works) will cause slowness as both tables contain fact_tables. If you want to learn more about Fact Tables and Dimension Tables, you need to check out the official documentation about Star schema as it plays a really important role when building Power BI dashboards.
You have Auto date/time in Time Intelligence Settings
In cases when you have a huge dataset that you are using to create your dashboard which requires time intelligence functions, it is overall suggested to create your own calendar date table (will post shortly after this newsletters on how to create one so stay tuned!) and not rely on the time dates within the dataset. If you are working with calculated measures or any sort of filters/slicers that involve dates, if using the date within the dataset, it will interact with all the rows of the dataset and try to calculate for the period it has been selected. If you use additional table as date table, then Power BI will use the date table as a way to filter the information in fact_table and the process will be much faster.
If you already have a built dashboard that relies on time intelligence functions, go to File/ Options and settings/Options/CURRENT FILE/Data Load and remove the ticker from Auto date/time. After doing so, you will notice that your dashboard will work much faster and the size will definitely be smaller, the reason for that is because Power BI will not update visuals every-time you apply a filter based on a date.
Please note, this is meant to be off only in cases when time intelligence functions are not required or in cases when you have a separate date table.
Your are not using the appropriate DAX syntax
DAX is a formula language that Power BI uses to do different types of calculations (learn more about DAX from the official documentation). Before creating calculated measures using DAX, make sure you are using the most efficient method on getting to your numbers. Let’s say you are creating a calculated measure based on if nested statements. If you have to use the same statement somewhere else, instead of rewriting it, you can create a variable and use the variable instead. This will make the calculation go faster as it will get the value from the variable instead of recalculating everything from scratch. DAX Studio is a free tool that helps you evaluate your DAX queries as well as gives you suggestions on how to improve it.
You are doing a heavy ETL in Power Editor
It is generally suggested that if you have to do a heavy ETL (learn more about ETL here), try to do it in the source of the data instead of bringing it in Power BI and applying it through Power Editor. Of course, there are some simple transformations which can be applied but you should try to avoid as much as you can. The reason for that is because, depending from the type of connection and if your dataset is large (which will take a while to load), after loading it will undergo all the steps in the transform phase which will make the process even more slower. An example of what you should avoid, is using Replace values. Imagine you have hundreds of thousands of rows, Power BI will evaluate each row separately to see if it matches the criteria you have selected to replace the value with.
Another example would be calculated columns instead of calculated measures. You should always avoid using calculated columns as that will calculate a value for every row you have in a dataset which will take very long time. Instead, you should use a calculated measure whenever possible because it will be calculated only if used in a visual and will get re-calculated in cases when a filter has been applied among visuals to the level it was plotted to.
You are not leveraging on Row-Level Security (RLS)
Probably more advanced topic than the rest, but definitely an important player when it comes to building dashboards which serve a large database of users. What RLS basically does is that it restricts the access to a specific user/group of users based on a specific criteria (learn more about RLS here).
As an example for that would be the dashboard we have created for a previous newsletter. As you can see from our dashboard below, in the top header, there is a Slicer which gives you the option to choose country/region for which you want to select. If this database would be large and if we would like to show this information only to the people of specific region, then using RLS we can apply that type of restriction. The way around it would be creating some sort of identifiers for each user (email and region), and basically Power BI would evaluate the region where it is coming from based on the email and it will show data only for that specific region instead of showing for all of the regions. The reason why this one is being discussed here is simply because by applying some RLS, dashboard will load much faster if showing the data only for one specific region to a specific user based on the criteria we have set up previously.
Of course, there might be a lot of other reasons why your Dashboard might be slow, but the ones I have listed here are the ones I have encountered the most. I would love to know your thoughts in this topic. Make sure you comment below for any issues you might have, and I will do my best to reply.
If you want to learn how to design a custom design dashboard, checkout my other post here How To Create Custom Color Themes In Power BI.