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.

Performance

  1. Limit the number of visuals in dashboards and reports
  2. 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.

  3. Use on-premises data gateway standard mode instead of personal mode
  4. 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.

  5. Use separate gateways for Power BI service live connection and scheduled data refresh
  6. 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.

  7. Limit complex measures and aggregations in data models
  8. 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.

  9. Use Star schema instead of the Snowflake schema when possible
  10. 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.

  11. Use slicers sparingly
  12. 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.

  13. Ensure reports and data sources are in the same region
  14. 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.

  15. Import only necessary fields and tables instead of entire data sets
  16. 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.

  17. Use templates (.PBIT files) to speed up and standardize report development instead of starting with an empty .PBIX file
  18. 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.

  19. Reduce queries
  20. 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.

  21. Avoid bi-directional and many-to-many relationships against high cardinality columns
  22. 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.

  23. Avoid using floating point data types
  24. Floating point data types can result in unpredictable round-off errors and can decrease the performance of reports.

  25. Replace the auto-generated date table with a custom date table in your model
  26. 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.

  27. Set IsAvailableinMdx to false on non-attribute columns
  28. 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.

  29. Reduce the amount of data loaded on the page
  30. 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.

  31. Use report backgrounds for static images
  32. 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.

  33. Choose Storage mode for tables appropriately
  34. Storage mode determines where your data for the table will be stored and how the queries are sent to the data source.

  35. Cross-check referential integrity check for relationships
  36. 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.

Security

  1. Enable Row-Level Security (RLS)
  2. 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.

  3. Use certified custom visuals
  4. 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.

  5. Categorize report data by business impact
  6. 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.

Design

  1. Ensure the cache update frequency aligns with the data source refresh frequency
  2. 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.

  3. Use white or light background colors
  4. White or light backgrounds are printer-friendly, ensuring your reports can be easily shared online and offline.

  5. Shorten numbers
  6. 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.

  7. Use tooltips to provide more information on visuals and metrics
  8. 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.

  9. Use names that are meaningful to users
  10. 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.

  11. Allow users to personalize visuals in a report
  12. 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.

  13. Avoid scrolls within the visual and on the page
  14. 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.

  15. Use drillthrough buttons instead of expecting users to right-click on data points
  16. 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.

  17. Optimize visual interactions
  18. 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.

References

Optimization Tools

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 practices

Read More →