Forum Discussion
Power BI slow report performance
Hello,
We have a dashboard built using Excel and Power BI. In Excel, there are 17 sheets, and each sheet contains more than 5 numeric columns, along with one date column. Accordingly, in Power BI, the main page is divided into 17 sections, and within each section, there are cards that display current values, values from the same period last year, the difference between them, and corresponding color indicators based on the difference. In total, there are 340 cards displayed.
The date column in each Excel sheet contains monthly data (on the last day of each month). However, some sheets have data that is updated later, meaning that not all sheets have the same date for the latest data.
We need to ensure that the latest data is displayed on the dashboard and, at the same time, there are year and month slicers on the main page to allow users to view data from previous months.
Currently, this page is loading very slowly. How can we optimize the performance to speed up the load time?
We are using Power BI Report Server on premise
6 Replies
- rajendraongole1
Super User
Hi fnifdalizada - Combine the 17 Excel sheets into a single table or fact table in your Power BI data model if possible. Add a column to differentiate each sheet (e.g., "Sheet Name" or "Category"). This reduces the number of queries Power BI has to process.
a. Remove Unnecessary Columns
Only load columns that are required for your visuals and calculations into the data model.
b. Partition Large Tables
If your Excel data has millions of rows, consider partitioning it into smaller chunks and filtering during load.
c. Optimize Query Folding
Ensure that Power Query transformations allow query folding (delegating operations back to the data source, such as SQL queries).
d. Data Refresh Strategy
Use incremental refresh to load only new or updated data instead of refreshing the entire dataset.reference links:
Optimization guide for Power BI - Power BI | Microsoft Learn
PowerBI Report Server Performance Tuning Best Practices (PBIRS)
Our excel files contains max 20 rows and our problem is not refresh time. Just we have 304 card visuals in one page and also 500+ measures for get last non blank value, arrows and determine color.
- danextian
Hi fnifdalizada
There are several possible reasons for the slow performance. It could be caused by complex measures or unnecessary columns, but the most obvious issue is the number of cards. Displaying 340 cards on a single page can overwhelm the rendering engine, especially with the added burden of conditional formatting. To address this, consider grouping sections or using drill-through pages to reduce the number of visuals displayed at once.
I cannot reduce number of cards unfortunately. But I can replace them in single table which are in same line. The page has 10 lines of cards. I can replace all cards with 10 table visual. Is it affect performance?
- tharunkumarRTK
Considering you referring to report page rendering performance, I would suggest you to check which visuals are taking longer. You can identify this from performance analyzer in Power BI desktop.
Copy the underlying visual DAX query and dtermine which dax measure expressions are cauing more effect. You would need DAX studio to idenfity this.
Make sure you are not using below dax patterns
Once you are done with this investigation, you can try to rewrite that expression differently and then check if that improves the performance. If you need any assistance then share the dax expression so that the amazing community members can help you. 🙂
Need a Power BI Consultation? Hire me on Upwork
Connect on LinkedIn
Did I answer your question? Mark my post as a solution! If I helped you, click on the Thumbs Up to give Kudos.
Proud to be a Super User!
rohit1991hi fnifdalizada
-
Optimize Data Model:
- Pre-aggregate data (e.g., monthly summaries).
- Remove unused columns.
- Move data from Excel to a database if possible.
-
Simplify Visuals:
- Reduce the number of cards by combining metrics or using summarized visuals.
- Use measures instead of calculated columns.
-
Efficient Date Handling:
- Use a central date table for consistent filtering.
- Filter to show only the latest available date per sheet dynamically.
-
Performance Tuning:
- Enable query folding for better performance.
- Use aggregations and reduce the data refresh frequency if appropriate.
-
Power BI Settings:
- Use Import mode instead of DirectQuery if data volume allows.
- Limit visuals on the main page and load detailed ones on drill-through pages.
-
