Power BI Best Practices Guide

Power BI Best Practices

Published: March 11, 2019; Last Updated: October 23, 2020 6 Minutes to Read

To improve the performance and security of enterprise-grade Power BI implementations, we share our best practices for architects and developers.

In the past three years, we have developed over 5,000 complex Power BI reports for our Fortune 500 customers and released 39 free custom visuals on Microsoft AppSource. Based on our extensive engineering experience with the Microsoft platform, we encourage you to consider the following techniques for your Power BI implementation.

  1. Limit the number of visuals in dashboards and reports
    • According to Microsoft's Optimization guide for Power BI, placing many visuals in a single report slows report performance.
    • Limit widget visuals to eight per report page and grids to one per page. Limit pages 30 points (cards: 1, gauges: 2, charts: 3, maps: 3, grids: 5).
    • Limit tiles to 10 per dashboard.
  2. To improve Power BI report performance, remove unnecessary interactions between visuals
    • By default, all visuals on a report page can interact with one another. For optimal report performance, interactivity should be minimized.
    • Reduce the number of queries fired at the back end and improve report performance by disabling unnecessary interactivity.
  3. Enable Row-Level Security (RLS)
    • 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.
    • Combining Power BI roles with roles in the back end can result in substantial performance gains.
    • Test all roles before rolling out to production.
  4. Use Microsoft AppSource certified custom visuals
    • Power BI certified visuals are AppSource visuals 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.
  5. Use preview feature of hierarchy slicers instead of custom visual
    • If you need to show hierarchy in slicers, enable the preview feature provided by the Power BI desktop instead of using of a custom visual.
  6. Provide data categorization for Power BI reports (HBI, MBI, LBI)
    • High Business Impact (HBI) data requires users to get 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 required security and how reports should be shared inside and outside the organization.
  7. Use on-premises data gateway instead of Personal Gateway
    • Personal Gateway takes data and imports it into Power BI.
    • Enterprise Gateway (on-premises data gateway) imports nothing, which is more efficient when working with large databases.
  8. Use different Power BI gateways for Live Connection and Scheduled Data Refresh
    • If the same gateway is used for Scheduled Data Refresh and Live Connection, Live Connection performance will slow down when Scheduled Data Refresh is active. Avoid this issue by creating separate gateways for Live Connection and Scheduled Data Refresh.
  9. Test custom visual performance on reports to ensure fast report load time; use an alternative visual if the chosen visual performs poorly
    • Uncertified custom visuals are generally not tested by the Power BI team. Custom visuals can perform poorly when handling large datasets or complex aggregations. If a custom visual performs poorly, consider replacing it with a different visual.
  10. Limit complicated complex measures and aggregations in data models
    • Push calculated columns and measures to the source where possible. The closer they are to the source, the higher the likelihood of improved performance.
    • Create calculated measures instead of calculated columns.
    • Use star schema to design data models.
  11. Use slicers sparingly
    • Slicers are a great way of allowing users to navigate data, but they come at a performance cost. Each slicer generates two queries: one gets the data, and the other fetches selection details.
    • Creating too many slicers negatively impacts performance. To evaluate which slicers are infrequently used, use the Filter pane and remove unnecessary slicers.
  12. Ensure the Power BI report and data source are in the same region
    • With the tenant and data source in the same region, you can reduce network latency. The results are faster data transfer and faster query execution.
  13. Import only necessary fields and tables instead of entire datasets
    • Ensure the model is as narrow and lean as possible.
    • Power BI works on columnar indexes; longer and leaner tables are preferred.
  14. Ensure the cache update frequency aligns with the data source refresh frequency
    • Cache update frequency should be set at similar intervals to data source refresh frequency.
    • By default, the Power BI cache update frequency is set to one hour. If, for example, your data set refreshes only once per day, you should update the cache frequency accordingly.
  15. Use white or light background colors
    • For users distributing printed reports, white or light backgrounds are printer friendly.
  16. Shorten numbers
    • Don’t exceed three or four numerals when displaying numbers. Display measures to one or two numerals left of the decimal point and scale for thousands or millions.
  17. Use Report Tooltip pages to provide more context for the highlighted measure
    • Report tooltips are a great way of sharing additional information on the metric.
    • Use limited visuals in Report Tooltip.
    • Ensure you select Tooltip field carefully (categorical or measure).
  18. Use templates (.PBIT files) to speed up and standardize report development instead of starting with an empty .PBIX
    • Templates can be saved with custom color palettes and themes pre-incorporated.
    • Templates ensure corporate branding is pre-applied to all pages.
    • Templates ensure connections to commonly used data sources are already in place.
    • Templates create commonly used DAX measures.
    • Source: LinkedIn
  19. Use names that are meaningful to your business users or intended audience
    • Power BI provides the ability to give aliases to report objects.
    • Avoid ambiguity when naming columns and measures.
    • Consider hiding unused columns in the data model.
    • Source: LinkedIn
  20. Reduce queries
    • Reduce the number of queries sent by Power BI using the settings for Slicers and Filters.
  21. Allow users to personalize visuals in a report
    • Help consumers gain further insights through an ad-hoc exploration of the visuals on a Power BI report.
    • Combine this feature with personal bookmarks to create a completely personalized reporting experience.
  22. Avoid scrolls within the visual and on page
    • Multiple scrolls on a single page lead to a negative user experience.
    • Limit your page size to the standard sizes as often as possible and use the Bookmark and Selection pane to toggle the visibility of visuals.
  23. Use drill through buttons to generate an intuitive user experience
    • Enable drill through buttons; these are more intuitive than right click on data points.
    • Use conditional formatting to make the text on the button context-driven.

Improve Power BI Performance by Optimizing DAX

For more tips on improving your Power BI performance, check out our DAX optimization guide.

References

Microsoft offers additional documents that provide a high-level framework for best practices. We strongly encourage you to review the following documents: