Power BI Best Practices

Using too many visuals in a single report slows report performance as each visual requires data processing and rendering. Limit widget visuals to eight per report page and grids to one per page. Limit tiles to 10 per dashboard.

Limiting the number of visuals reduces the amount of data that needs to be fetched and displayed at once.

Also consider using Power BI’s new card visual, capable of displaying multiple cards within a single container. This improves report performance and minimizes the number of queries fired by consolidating information into a single query.

The personal mode imports data into Power BI, which can cause resource limitations and performance issues (particularly with large databases). On the other hand, the standard mode retains data in its original location.

As the standard mode doesn't import any data into Power BI, it is more efficient. Using this gateway minimizes data duplication, reduces memory usage, and avoids potential performance bottlenecks.

A Power BI service live connection (DirectQuery) maintains a real-time connection with data sources. Scheduled data refresh updates imported data at set times. Using the same gateway for both live connections and scheduled data refresh can overload it during refreshes, slowing live connections.

Separating the gateways ensures that both functions can work efficiently without affecting each other.

Complex measures and aggregations in data models can slow down query performance. Using calculated measures, which is computed during query execution, are more efficient than calculated columns. Applying filters can also aid in this process.

Pushing calculations to the source is recommended as it offloads the processing to the data source, optimizing query processing. Being closer to the source can increase performance speed.

The star schema is a simpler and more denormalized data model compared to the snowflake schema. The snowflake schema involves more complex queries due to multiple related tables.

The star schema accelerates query execution and eases maintenance by reducing required joins. It improves query efficiency, reduces data redundancy, and simplifies report development.

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.

A Power BI tenant is the dedicated environment that houses an organization's data, reports, and dashboards. Microsoft operates data centers worldwide to provide and hosts its services. Each data center is in a specific region.

Hosting both tenant and data source in the same region minimizes network latency. This enables quicker data transfer, query execution, and thus faster data retrieval and report rendering.

Partitioning is a technique that divides large tables into smaller subsets called partitions based on specific criteria. A columnar index is a type of database index that stores and organizes data by columns rather than rows.

Power BI uses columnar indexes, so longer, leaner tables perform better. The partitioning approach, where only necessary fields and tables are imported, improves data loading efficiency, and reduces resource consumption. This leads to faster report performance.

Templates enable you to develop standardized and 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. These all help streamline the report creation process and allow more time for analysis.

Use Power BI's query reduction settings to lower query frequency.

For slicers, select the Add an Apply button to each slicer to apply changes when you’re ready option. With this, changes to slicer selections are only applied when the user clicks the Apply button. This reduces the frequency of queries sent.

For filters, the Add a single Apply button to the filter pane to apply changes at once (preview) option consolidates filter changes and applies them all at once, further minimizing query volume.

Reducing the number of queries improves report performance, especially when dealing with complex reports and large datasets.

Bi-directional relationships refer to the two-way data flow between tables. Many-to-many relationships refer to multiple records in one table being related to multiple records in another. High cardinality columns are columns in a table with many unique values relative to the total number of rows.

When applied to high-cardinality columns, these relationships can significantly impact report performance due to increased data processing and query complexity.

Avoid these relationships to optimize query performance and reduce resource consumption, leading to faster report rendering.

Floating point data types, such as float and double, can lead to round-off errors and less predictable calculations. They also often require more processing power, which can slow down report performance.

By avoiding these data types, you ensure more accurate data representation and improve report efficiency.

Replacing auto-generated date tables with custom ones reduces model size and increases performance. The auto-generated date table creates a separate table for each date column, which can bloat the model. A singular custom date table, with date and time split for better compression, streamlines the model and allows for more efficient use of time-series functions in Power BI.

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.

Disabling attribute hierarchy for non-attribute columns, like measures, streamlines the data model. This setting reduces the overall data size and load time, increasing report performance by focusing on essential data and calculations.

Using bookmarks, drillthrough pages, and tooltips can significantly reduce the amount of data loaded on a page. This approach improves page load times and improved the user experience, especially on landing pages where quick access to information is crucial.

Employing report backgrounds for static images instead of multiple visuals reduces the performance cost. This strategy uses fewer resources than individual visuals, ensuring the report remains responsive and efficient.

Choosing the right storage mode (Import, DirectQuery, or Dual) based on your data aggregation needs and visualization requirements is crucial. This decision impacts where your data is stored and how queries are processed, directly affecting the report's responsiveness and efficiency.

In DirectQuery sources with enforced primary keys, verify the Assume Referential Integrity setting in relationships. If set to Off, Power BI defaults to slower outer joins instead of inner joins. Adjusting this setting can optimize query performance and speed.

Row-level security limits data access based on user roles. With RLS, Power BI only imports data the user is authorized to view.

Simplifying RLS logic and moving complex calculations to the source is also recommended. This strategy reduces the computational load on Power BI by keeping the RLS calculations straightforward, improving overall performance.

Power BI-certified visuals are custom visuals on AppSource that have passed rigorous quality testing. Certified custom visuals are also the only custom visuals that can be viewed in Export to PowerPoint mode and email subscriptions.

Using Power BI-certified visuals is preferred as Microsoft verifies the visuals’ high performance and robustness.

Using sensitivity labels in Power BI to categorize data (high, medium, low impact) raises user awareness about data security. 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.

The use of sensitivity labels aids in managing data sharing and ensures appropriate handling of sensitive information inside and outside of an organization.

By default, the Power BI cache update frequency is set to one hour. Synchronizing the cache update frequency with the data source refresh frequency ensures data accuracy and optimizes report performance. This alignment prevents unnecessary data processing and keeps the information up-to-date

White or light backgrounds in reports make them printer-friendly and easier to share. This approach ensures reports are accessible both online and offline, improving their utility.

Limiting displayed numbers to four numerals and decimal points to two decimal points improves readability and consistency across reports. This practice makes reports easier to understand and analyze, especially when scaling numbers for thousands or millions.

Tooltips provide extra context in a compact form, making efficient use of report space. Careful selection of visuals in tooltips ensures information is helpful without being overwhelming, elevating the user experience.

Assigning clear, business-friendly names to columns and measures, and hiding unused columns, reduces user confusion. This clarity improves the usability of the reports, making them more intuitive and effective.

Enable the Personalize visuals option in report settings when sharing reports with users. This fosters deeper engagement and exploration. Combining this feature with personal bookmarks empowers users, facilitating self-service BI.

Minimizing scrolls within visuals and on the page improves user experience. Having a lot of scrolls can be cumbersome for users, especially when trying to understand a complex visualization or dataset. Scrolling can disrupt the flow of data analysis and make it difficult to get a comprehensive view of the information presented.

Avoiding non-standard report sizes is also ideal as these sizes may not display consistently across different devices and platforms, resulting in poor responsiveness and a disjointed visual presentation.

Drillthrough buttons, with context-driven conditional formatting, offer an intuitive way for users to explore data. This approach is more user-friendly than right-click navigation, improving the report.

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 DirectQuery reports, use Optimize Ribbon to optimize reports for interactivity.