Power BI Best Practices
Improve report performance, security, and design
This guide covers the 30 best practices you need to improve your Power BI report performance, security, and design. As the 2021 Microsoft Power BI Partner of the Year, we have proven expertise in Power BI migration and optimization.
- Limit the number of visuals in dashboards and reports
- Use on-premises data gateway standard mode instead of personal mode
- Use separate gateways for Power BI service live connection and scheduled data refresh
- Limit complex measures and aggregations in data models
- Use Star schema instead of the Snowflake schema when possible
- Use slicers sparingly
- Ensure reports and data sources are in the same region
- Import only necessary fields and tables instead of entire data sets
- Use templates (.PBIT files) to speed up and standardize report development instead of starting with an empty .PBIX file
- Reduce queries
- Avoid bi-directional and many-to-many relationships against high cardinality columns
- Avoid using floating point data types
- Replace the auto-generated date table with a custom date table in your model
- Set IsAvailableinMdx to false on non-attribute columns
- Reduce the amount of data loaded on the page
- Use report backgrounds for static images
- Choose Storage mode for tables appropriately
- Cross-check referential integrity check for relationships
Using too many visuals on a single report slows report performance. Limit widget visuals to eight per report page and grids to one per page. Limit tiles to 10 per dashboard.
On-premises data gateway personal mode imports data into Power BI. On-premises data gateway standard mode imports nothing, which is more efficient when working with large databases.
If you use the same gateway for a scheduled refresh and a live connection, the live connection performance will slow down during the scheduled refresh.
Create calculated measures instead of calculated columns. Where possible, push calculated columns and measures to the source. The closer they are to the source, the faster they are likely to perform.
The Snowflake schema has a complex query structure, making it difficult to implement changes. On the other hand, Star schema is easy to read, uses fewer joins, and tends to reduce data redundancy.
Slicers are a great way of allowing users to navigate data, but they come at a performance cost. Each slicer generates two queries: one fetches the data and the other fetches selection details. Adding too many slicers drastically slows performance. Use the Filter pane to evaluate and remove unnecessary slicers.
With the tenant and data source in the same region, you can limit the effects of network latency. Sharing a region ensures faster data transfer and faster query execution.
Ensure the model is as narrow and lean as possible. Power BI works on columnar indexes, meaning longer and leaner tables perform better. When you need to import a large table, partition it and process multiple partitions in parallel.
Templates enable you to develop branded reports faster. With templates, you can save custom color palettes and themes, ensuring corporate branding is pre-applied to all pages. Templates automatically connect to commonly used data sources and offer commonly used DAX measures as well.
Reduce the number of queries sent by Power BI using the Query reduction settings. For slicers, select the “Add an Apply button to each slicer to apply changes when you’re ready” option. For filters, select the “Add a single Apply button to the filter pane to apply changes at once (preview)” option.
Many-to-many and bi-directional relationships navigate more pathways and check more data points. As a result, bi-directional relationships against high-cardinality columns negatively impact report performance.
Floating point data types can result in unpredictable round-off errors and can decrease the performance of reports.
Using a date table enables you to use the time-series function in Power BI. However, the auto-generated date table creates a date table for each date column, drastically increasing the model size. You can use a single date table to reduce the model size, placing any required relationships in fact tables. When building your date table, split the date and time to improve data compression. Once you learn how to turn off the auto-generated date table and how to set and use custom date tables, doing it the next time will be an easy task.
Disable attribute hierarchy for measure columns and for any columns you don’t want end users to use. This reduces data size and load time.
Use bookmarks, drillthrough pages, and tooltips to reduce the amount of data loaded on the page. This improves page load time, especially for landing pages.
For static images, use report backgrounds rather than multiple visuals. This conveys the same information to the end user at a fraction of the performance cost.
Storage mode determines where your data for the table will be stored and how the queries are sent to the data source.
For Direct Query Sources where the Primary key is enforced, cross-check the “Assume Referential Integrity” value in Relationship. By default, this value is set to “Off”. When it is marked as “Off” it performs Outer Join instead of Inner Join which can be slower.
- Enable Row-Level Security (RLS)
- Use certified custom visuals
- Categorize report data by business impact
Row-Level Security restricts user access to certain rows in a database depending on the characteristics (role) of the user executing a query. With RLS, Power BI only imports data the user is authorized to view.
Power BI-certified visuals are custom visuals on AppSource that have passed rigorous quality testing. Microsoft verifies that certified custom visuals have robust, high-performance code. Certified custom visuals are the only custom visuals that can be viewed in Export to PowerPoint mode and email subscriptions.
Use Power BI sensitivity labels to classify data as high, medium, or low business impact. High Business Impact (HBI) data requires users to request a policy exception to share the data externally. Low Business Impact (LBI) and Medium Business Impact (MBI) data do not require exceptions. By using Power BI data sensitivity labels, you raise user awareness about security and how reports should be shared inside and outside the organization.
- Ensure the cache update frequency aligns with the data source refresh frequency
- Use white or light background colors
- Shorten numbers
- Use tooltips to provide more information on visuals and metrics
- Use names that are meaningful to users
- Allow users to personalize visuals in a report
- Avoid scrolls within the visual and on the page
- Use drillthrough buttons instead of expecting users to right-click on data points
- Optimize visual interactions
By default, the Power BI cache update frequency is set to one hour. The cache update frequency should be set at similar intervals to the data source refresh frequency. For example, if your data set refreshes only once per day, you should update the cache frequency accordingly. This improves report performance and accuracy for end users.
White or light backgrounds are printer-friendly, ensuring your reports can be easily shared online and offline.
At most, show up to four numerals when displaying numbers. Be consistent across decimal points, limiting measures to two numerals to the right of the decimal point. When necessary, scale for thousands or millions.
Report tooltips are a great way of sharing additional information in a limited report space. Limit which visuals you use in report tooltips to ensure information isn’t overwhelming.
Power BI allows you to give aliases to report objects. Avoid ambiguity for end users when naming columns and measures by using business-friendly names. Consider hiding unused columns to avoid confusing users.
Enable “Personalize visuals” in report settings when sharing reports with users. This enables users to gain further insights through ad-hoc exploration. Combine this feature with personal bookmarks to enable self-service BI.
Multiple scrolls on a single page lead to a negative user experience. Limit your page size to the standard report sizes when possible. Use the Bookmark and Selection pane to toggle the visibility of visuals if needed.
Drillthrough buttons are more intuitive than right-clicking because they clearly signal actions and results to users. Use conditional formatting to ensure button texts are context-driven, further improving the user experience.
Every visual interacts with other visuals on the page either by cross-filtering or cross-highlighting. Evaluate the interactions which are relevant to the report audience and remove the unnecessary ones. For Direct Query reports, use Optimize Ribbon to optimize reports for interactivity.
- Tips for designing a great Power BI dashboard – Microsoft Corporation, published June 29, 2023
- Optimization guide for Power BI – Microsoft Corporation, published February 26, 2023
- Visualizations in Power BI reports – Microsoft Corporation, published February 2, 2023
Dr. Power BI analyzes your reports and prescribes solutions for both UX and DAX errors, enabling you to access faster, more intuitive reports. Try Dr. Power BI free here.
DAX Best Practice Guide
Improve your Power BI performance with our 22 DAX best practicesRead More →