September 8th
February 22ndPrefer step-by-step video instructions?
Join 2,000+ people who've already watched the 1-hour webinar recording with Andrej Lapajne, the Founder of Zebra BI and a Microsoft MVP.
Power BI Performance Optimization Checklist
PRO Trial
Available for Free!
Join over 1,500,000 Zebra BI users who can produce insightful reports every time.
Time is money – we know it all too well. And reports that take forever to load are a waste of both. That's why we're now introducing a list of actionable tips that will help with optimizing Power BI performance. Follow them closely to fine-tune your reports and make them run up to 10x faster.
Ready to rev them up? Let's get cracking.
1. Analyze your performance
Aim for 0.5–1s total load time per page.
- Run the Performance analyzer in Power BI Desktop. This will help you establish the current load times and identify the bottlenecks.
- Copy the query to DAX Studio if necessary. This is a great tool for a more detailed performance analysis. It can be especially useful in case of database-related problems.
- Export logs and analyze the details if necessary. Exporting a Performance analyzer report as a JSON file can provide you with very detalied information on queries, their load time, etc. For basic analysis, however, the data provided by the Performance analyzer should suffice.
2. Speed up your data model
- Do your data prep. Well-organized data is vital for optimizing Power BI performance. Typically, you should prepare it in Power Query or at the data source level (such as SQL server) – and not in DAX. Here's how you can go about it.
- Combine or append different fact tables into one main fact table.
- Transform your hierarchies and attributes into (single-)table dimensions.
- Filter out/remove any columns you don't really need.
- Implement a clean STAR SCHEMA. When it comes to optimizing Power BI performance, this is the most appropriate data model. In theory, the flat table model can be faster, but DAX is optimized for the star schema. As a result, the latter will outperform the flat table in most real-case scenarios. It also requires much less RAM & storage (more on star schema vs. flat table).
Here's a quick break-down of what you can do.
Fact Table:
- Measures
- Implicit (hide)
- Explicit (use only these)
- Reduce precision (no more than 2 decimals)
- Foreign keys
- Integer only!
- As short as possible
- Hide foreign keys from report view – so the fact table shows only the explicit measures for the users
- Reduce granularity
Dimensions:
- Hide foreign keys (IDs)
- Only necessary columns (use lean fact tables – long, not wide)
- Calendar key: Date
- Split Date/Time into Date and Time columns
- Avoid calculated columns (if you absolutely need them, add them in Power Query)
- Turn off Power BI’s auto-date table
- Relationships to fact table: 1-to-many
- Push calculations down the BI stack. Many calculations can be done in DAX (e.g. calendar dimension calculations). In case of poor performance, however, it's better to move them down the BI stack.
- Calculate data in Power Query or in your source database (not in DAX)
- Use simple DAX formulas; only non-additive measures (use DIVIDE)
- Choose the right connection type. There are several methods available, with their respective pros and cons. To maximize your performance, the general rule of thumb is: Import > Live > Composite > DirectQuery (DirectQuery connection type is 4–5x slower at best; only use it if you need real-time data).
- Simplify RLS. Row-level security can drastically reduce your performance.
- Avoid RLS if possible
- If not, a clean star schema model is a must – try to edit it with simple rules (no lookups, no joins) & only apply it to dimensions
- Consider alternative designs – split the model and reports for different audiences, publish to separate workspaces
3. Speed up your reports and visuals through design
- Reduce the number of visuals per page. Fewer visuals does not necessarily mean less information! Consider using multi-card type visuals, they can be total life-savers!
- Implement an efficient report layout pattern. Organize your report/dashboard page so that it displays high-level data first, and then gradually progresses to a more detailed view (Overview > Details >
More details …).
Extra tip: Lazy-load your visuals with bookmarks and hidden visuals (more info)
Optimizing Power BI performance: RECAP
As we've seen, there's so much you can do to speed up your reports and improve your user experience. TL; DR? Here's a quick and to-the-point summary of our best-practice tips.
Additional materials & information
If you're as fascinated as we are by Power BI performance optimization possibilities, we've got great news – there's always more to learn! Feel free to check out the links below to dive deeper into this.
- Power BI Performance Optimization: How to Make Your Reports Run Up to 10X Faster: https://zebrabi.com/power-bi-performance-optimization/
- DAX Studio download:
https://daxstudio.org/ - SQLBI:
https://www.sqlbi.com/articles/power-bi-star-schema-or-single-table/ - SQLBI: Optimizing DAX SWITCH – Part 1:
https://www.sqlbi.com/articles/understanding-the-optimization-of-switch/ - SQLBI: Optimizing DAX SWITCH – Part 2:
https://www.sqlbi.com/articles/optimizing-if-and-switch-expressions-using-variables/ - Phil Seamark (MSFT):
https://dax.tips/2019/12/24/use-bookmarks-to-lazyload-visuals/ - Daniel Otykier - Tabular Editor:
https://tabulareditor.com/downloads/ - Daniel Otykier - Best Practice Analyzer:
https://docs.tabulareditor.com/te2/Best-Practice-Analyzer.html - Michael Kovalsky (MSFT) - Best Practice Analyzer Rules:
https://powerbi.microsoft.com/en-au/blog/best-practice-rules-to-improve-your-models-performance/ - Michael Kovalsky (MSFT) - Best Practice Analyzer Rules v1.1:
https://powerbi.microsoft.com/en-my/blog/best-practice-rules-to-improve-your-models-performance-and-design-v1-1/
