Eugene Lebedev is a Power BI consultant by background. Before founding Vidi Corp in 2021 he created Power BI reporting for Autodesk. His Power Bi reports were used by VPs of Finance and C-suite of Autodesk. As part of Vidi Corp, Eugene created dashboards for Google, Teleperformance, Delta Airlines and 200+ clients worldwide.
Report optimisation in Power BI refers to the process of improving the load speed of the visuals and the refresh time of the reports. There are various techniques and best practices to enhance the loading time, responsiveness, and overall user experience of Power BI reports.
The following are some common areas of focus for report optimisation in Power BI:
Let’s look into what each of these features does in more detail.
The following are some common areas of focus for data modelling optimisation in Power BI.
Removing a column in PowerQuery:
Hiding a column:
The following are some common areas of focus for data modelling optimisation in Power BI.
Query folding in Power BI is a technique used to optimise query performance by pushing data transformation steps back to the data source. When query folding is achieved, Power BI sends a modified query to the data source, which performs the required transformations and returns the results to Power BI. It can be used on most relational databases but if you want to know whether is applicable to your data source, use the below steps:
Let’s understand the process to implement query folding to improve the performance of the report. We have created a report using SQL as a data source and the data table as per the below schema:
2. Provide the SQL server details and server connectivity mode then ‘Click Ok’.
3. Select the required view or table from the list and click on transform data.
4. It loads the data to Power BI and will show it in the Power query.
5. In order to check whether this data source supports Query folding or not, follow the above steps. (Right-click on the Navigation or any applied steps and see the Native query option.)
6. So our database supports query folding.
Assume there is a requirement to show only the data with certain filters. In our report, we will have to show only data where YearOpened is greater than 1990 AND NumberEmployees is greater than 10.
We can easily achieve it by applying filters in the Power Query as follows:
After applying these filters, close and apply will load the filtered data to the data view. Since our database supports query folding then we can push these transformations(Filters) to the database instead of doing them at the Power Query level. Let’s understand the process to implement it using query folding:
2. Copy the query and double-click on the source item in the applied steps .
3. Click on the advance action of the window and paste the copied SQL query to SQL Statement box.
4. Add ‘WHERE’ clause to the query at the end with filters condition as per requirements. Remember we need to provide a database name which is mandatory for query folding.
5. Click ‘OK’ and see filtered data loaded to Power BI and there are no steps logged in the applied steps section.
This enables a better query performance of the data and will improve the data refresh time and report performance.
This is also another important method to optimise your report. You can disable loading the table if you are not planning to use it as a dimension or a fact table. This is commonly done for tables that are used for merge or append functions.
Assume we have 3 tables loaded to the Power BI and append these tables into a single table. Now if we load all tables then it would be a duplicate data instead disable the load of these tables and load only the final appended table to the data view.
In order to enable or disable a load :
Power BI will not load the selected table to the data view.
The following are some common areas of focus for report designing optimisation in Power BI.
Minimise the number of visuals and elements on a report page to improve rendering and interaction performance.
Avoid using too many slicers on a page as each slicer can impact performance.
Use appropriate visual types, reduce unnecessary formatting, and avoid complex visual interactions to improve rendering performance.
The following are some common areas of focus for performance monitoring and optimisation in Power BI.
Utilize Power BI’s Performance Analyzer tool to identify and troubleshoot performance bottlenecks.
Keep track of query execution times and identify slow-running queries to optimise them.
Analyse usage metrics and identify reports or visuals that are causing performance issues.
Schedule data refresh during off-peak hours and optimise queries used in data refresh operations.
Configure and optimise the Power BI Gateway for efficient data transfer between on-premises and cloud-based data sources.
Allocate appropriate resources to Power BI, including CPU, memory, and disk, to handle the workload efficiently.
If feasible, use DirectQuery or Live Connection instead of importing data to reduce memory consumption and improve performance.
This article has highlighted the benefits of using a calendar table and how to add the table using various methods. Adding a calendar table in Power BI is important for time intelligence calculations, data aggregation, visualisation, customisation, and improved performance.
