Seeking Advice: Optimizing Performance with Large Datasets and Complex Joins in Power BI

manoj_0911 ,

1. Use star Schema

2. Use only numeric keys to join

3. Do not use bi-directional joins 

4. Prefer measures whenever possible.

5. For complex calculation check if part of that can be moved as calculated columns

6. Try not to use related in measure unless it is most needed

7. Prefer Window, Offset, Index, Rank and Rownumber, wherever possible

8. If measures are too complex, try to break the calculation and move part of it a calculated column

others

Best Practices
https://maqsoftware.com/expertise/powerbi/power-bi-best-practices
https://docs.microsoft.com/en-us/power-bi/guidance/power-bi-optimization
https://www.c-sharpcorner.com/article/power-bi-best-practices-part-1/
https://www.knowledgehut.com/blog/business-intelligence-and-visualization/power-bi-best-practices
https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-The-Do-s-and-Don-ts-of-Power-BI-Relationships/td-p/712566
https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-My-Power-BI-report-is-slow-What-should-I-do/td-p/712567
https://community.powerbi.com/t5/MBAS-Gallery/Aggregation-and-composite-model-Microsoft-Power-BI-fast-and/td-p/712871
direct query performance
https://insightsquest.com/2020/05/25/simple-directquery-performance-tips/
https://locusit.com/performance-management/power-bi-directquery-performance-improvements/
https://community.powerbi.com/t5/MBAS-Gallery/Aggregation-and-composite-model-Microsoft-Power-BI-fast-and/m-p/712871

Hi manoj_0911 

Good answer！ amitchandak 

I have some suggestions that I would like to add:

Optimize your visualizations by limiting the number of visuals per page, using slicers sparingly, choosing the right chart types, and avoiding unnecessary formatting and interactions. Too many visuals can slow down the data processing and rendering, Slicers are useful for filtering data, but they can also generate many queries and affect performance, so use them only when necessary. Chart types can also impact performance, as some are more complex and resource-intensive than others, such as maps, gauges, and waterfall charts. Formatting and interactions can also add to the complexity and load time of the visuals, so avoid using them unless they add value to the report.

Optimize your environment by using separate gateways for live connections and scheduled refreshes, configuring the refresh frequency and retention policy, and monitoring the capacity and network performance. Using the same gateway for both live connections and scheduled refreshes can overload it and slow down the performance, so it is better to use separate gateways for each function. Refresh frequency and retention policy can also affect the performance, as they determine how often the data is updated and how long it is stored. You can adjust these settings according to your needs and data source characteristics. Capacity and network performance can also influence the performance, as they determine the resources and bandwidth available for Power BI. You can monitor these factors using the Power BI admin portal and the Power BI Premium metrics app.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.