Power BI Best Practice Guide

Last Updated: August 16, 2021 12 Minutes to Read



This Power BI best practice guide will help you optimize your Power BI reports and dashboards to be faster, more secure, and more user-friendly. As the 2021 Microsoft Power BI Partner of the Year, we are recognized for our expertise in implementing business intelligence and analytics solutions.

We have compiled these best practices based on our experience, including:

How To Improve Power BI Security

  1. 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 substantially improve performance.

  2. Use certified custom visuals

    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.

  3. Categorize report data by business impact

    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.

How to Make Power BI Faster

  1. Limit the number of visuals in dashboards and reports

    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. In general, limit pages 30 points, assuming each type of visual is worth a different number of points:

      Cards: 1
      Gauges: 2
      Charts: 3
      Maps: 3
      Grids: 5
  2. Remove unnecessary interactions between visuals

    By default, all visuals on a report page can interact with one another. By disabling unnecessary interactions, you reduce the number of queries fired at the back end, which improves report performance.

  3. Use on-premises data gateway instead of Personal Gateway

    Personal Gateway imports data into Power BI. On-premises data gateway (also known as Enterprise Gateway) imports nothing, which is more efficient when working with large databases.

  4. Use separate gateways for Power BI service live connection and scheduled data refresh

    If you use the same gateway for a scheduled refresh and a live connection, live connection performance will slow down during the scheduled refresh.

  5. Test custom visual performance before use

    Custom visuals can perform poorly when handling large datasets or complex aggregations. Uncertified custom visuals are generally not tested by the Power BI team. If a custom visual performs poorly, consider replacing it with a different visual.

  6. Limit complex measures and aggregations in data models

    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.

  7. Use Star schema instead of Snowflake schema when possible

    Snowflake schema has a complex query structure, making it difficult to implement changes. Star schema is easy to read, uses fewer joins, and tends to reduce data redundancy.

  8. 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 fetches the data, the other fetches selection details. Adding too many slicers drastically slows performance. To remove unnecessary slicers, use the Filter pane to evaluate which slicers are used the least.

  9. Ensure reports and data sources are in the same region

    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.

  10. Import only necessary fields and tables instead of entire data sets

    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.

  11. Use templates (.PBIT files) to speed up and standardize report development instead of starting with an empty .PBIX file

    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 also automatically connect to commonly used data sources and offer commonly used DAX measures.

  12. Reduce queries

    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 “Add a single Apply button to the filter pane to apply changes at once (preview).”

  13. Avoid bi-directional and many-to-many relationships against high cardinality columns

    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.

  14. Avoid using floating point data types

    Floating point data types can result in unpredictable round-off errors and can decrease the performance of reports.

  15. Replace the auto-generated date table with a custom date table in your model

    Using a date table enables you to leverage 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 model size, placing any required relationships in fact tables. When building your date table, split the date and time to improve data compression. Find out how to turn off the auto-generated date table here and how to set and use custom date tables here.

  16. Set IsAvailableinMdx to false on non-attribute columns

    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.

    Source: Chris Webb's BI Blog

  17. Reduce the amount of data loaded on page load

    Use bookmarks, drill-through pages, and tooltips to reduce the amount of data loaded on page load. This improves page load time for landing pages.

  18. Use report backgrounds for static images

    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.

How To Improve the User Experience (UX) of Reports and Dashboards

  1. Ensure the cache update frequency aligns with the data source refresh frequency

    By default, the Power BI cache update frequency is set to one hour. Cache update frequency should be set at similar intervals to data source refresh frequency. If, for example, your data set refreshes only once per day, you should update the cache frequency accordingly. This improves report performance and accuracy for end users.

  2. Use white or light background colors

    White or light backgrounds are printer friendly, ensuring your reports can be easily shared online and offline.

  3. Shorten numbers

    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.

  4. Use tooltips to provide more information on visuals and metrics

    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.

  5. Use names that are meaningful to users

    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. To avoid confusing users, consider hiding unused columns.

  6. Allow users to personalize visuals in a report

    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.

  7. 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 report sizes when possible. Use the Bookmark and Selection pane to toggle the visibility of visuals if needed.

  8. Use drillthrough buttons instead of expecting users to right-click on data points

    Drillthrough buttons are more intuitive than right-click because they clearly signal actions and results to users. Use conditional formatting to ensure button text is context-driven, further enhancing the user experience.

Improving Power BI Performance by Optimizing DAX

For more tips on improving your Power BI performance, check out our DAX Best Practice Guide.

To quickly assess opportunities in your reports and dashboards, check out our free Dr. Power BI tool. Dr. Power BI assesses your Power BI reports to identify quick solutions for common problems.


References


Download the Infographic

Easily print or share our Power BI best practices by downloading the Power BI best practices infographics: