Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
TotalAnonymous
Frequent Visitor

Power BI Slow Report Performance - Service

Dear Community, I hope you're doing well!

I'd like to ask about PBI Slow report performance in Service. 

First of all, I'm sorry I can't show you the table visual since it contains confidential data.

We have 5 years POS data point. I have set the granularity level only by product category not in SKU level. Since the time period is weekly basis and we also include retailer code. Based on the consideration, if I set the granularity level by SKU as well, it will grow my data size. To check the performance, I created blank page and try to switch the page to “By Brand”. As you can see the DAX query of my table is 219 ms.

TotalAnonymous_0-1728620384554.png


Looking at this number, I tried to copy the query into DAX studio. Before running the query, I cleared the cache by hit “Clear on Run” then click “run”. As you can see in the image below, the total duration of the table creation is 67 ms and SE queries number is 13. I watched Guy in a Cube video that benchmark of optimized DAX query is Duration < 120 ms and SE queries < 20.

TotalAnonymous_1-1728620450765.png

 

However, I’m still confused why our users experience slow performance from their end. 

For your information, my users are mostly located in Japan and I'm in Indonesia. 

I checked "About Power BI" section and the data is stored in Ireland.

TotalAnonymous_2-1728620652883.png

 



Do you have any recommended advice for me? I really don't know what to do since the number of visuals, based on my opinion, is not too many on that page. 

Thank you.







1 ACCEPTED SOLUTION

Have a look into the capacity metric, see if you can identify any processes that take significantly long or it could also be throttling on your capacity if it hits the limit?


View solution in original post

5 REPLIES 5
rajendraongole1
Super User
Super User

Hi @TotalAnonymous - Your users are in Japan, and the Power BI Service data is stored in Ireland. The geographical distance could introduce latency when users interact with reports.

Recommendation: Consider moving your dataset storage region closer to where your users are based (Japan) to minimize latency. Alternatively, using Power BI Premium capacities with multi-geo options could allow for closer storage to users.

The time it takes to load a report isn't only dependent on query performance. Complex visuals (or too many visuals) on a page can slow down rendering.

Recommendation: Optimize your visuals by reducing the number of charts or using lighter visuals. You can also look into aggregating data at higher levels to reduce the rendering load.

Use Performance Analyzer in Power BI to check which visuals take the longest to render and see if simplifying them improves performance.

Review your gateway cluster and settings. Consider whether scaling up the gateway capacity or adding more nodes could help.

Ensure your gateway is located as close as possible to your data source for quicker data refreshes and lower response times.

Where possible, replace custom visuals with native visuals in Power BI. If custom visuals are required, try to minimize their use or find optimized versions.

Vertipaq Analyzer to ensure that your DAX measures are highly efficient. Complex calculations can be offloaded using calculated columns in your model where appropriate.

 

Hope the above suggestions helps in optimization.

 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Thanks @rajendraongole1 , for your feedback!

As I mentioned, I've tested my DAX Query for "table creation" using DAX studio by clearing the cache as shown in the image above. The largest number of duration time came from the Table. That's why I confuse our users have taken long while (around 15-40 seconds) to load the table. For the visuals, I use the native rather than the custom one. On that page, I have 1 Slicer, 2 text boxes, 1 bookmark navigator, and 1 table. So, I'm not sure if it necessary to reduce number of visuals again 🙂

On that table, I have 9 fields. 2 among them are column field from dimension table. While the rest are measures.  

TotalAnonymous_1-1728625767391.png

 



Our workspace also using premium capacities as shown in the image below. 

TotalAnonymous_0-1728625527306.png

 


Furthermore, your recommendation about dataset storage movement and Gateway Cluster are interesting. Have you ever done it before? are they difficult to be done?

Have a look into the capacity metric, see if you can identify any processes that take significantly long or it could also be throttling on your capacity if it hits the limit?


Thanks @Tutu_in_YYC, for your reply! Since I'm not a capacity admin, I tried to reach out someone who has access to capacity metric to see how our workspace behave. I've been showed the graph and it seems that the capacity is constantly overloaded. Lately, Background% achieve the %CU limit. Since our workspace is assigned to the regional capacity, i don't know how to solve the issue. Because scaling up the capacity requires approval from higher level.

If your report doesnt require high compute nor it is huge size wise, you can try removing premium capacity from the workspace, and just use the shared capacity. 


Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors