Best Practices for Power BI: Data Modelling, Efficiency & Layout

In this guide, we present 26 best practices for Power BI that can enhance your data modelling, performance, design, and report security. By implementing these techniques, you can significantly elevate the reporting experience for your users and boost the success of your projects. Our experience as Power BI consultants encompasses over 1,000 development projects for more than 600 customers, drawing insights from both our hands-on expertise and Microsoft's official recommendations.

Avoid Many-to-Many Relationships

• Bi-Directional Cardinality Relationships
• Choose Appropriate Data Types
• Remove Unnecessary Columns
• Use Query Folding For SQL-Based Data Sources
• Avoid Loading Unnecessary Tables
• Avoid Direct Query
• Aggregate Data

Power BI Performance Best Practices

• Reduce Number Of Graphs
• Use fewer slicers
• Avoid Custom Visuals
• Optimise Your DAX
• Use More Powerful Hardware
• Adjust Data Gateway Settings

Power BI Design Best Practices

• Follow Data Visualisation Principles
• Select Appropriate Graphs
• Use F-Shaped Layout
• Reduce the Amount Of Visual Noise
• Make Use Of White Space
• Choose Colours Strategically

Power BI Security Best Practices

• Use Row-Level Security
• Assign Appropriate Roles In Workspaces
• Assign Sensitivity Labels
• Power BI Report Server for Sensitive Data
• Monitor User Activity
• Power BI Data Modelling Best Practices

Optimising your data model is crucial for speeding up dataset refreshes and loading visual elements on a page. It's important to note that Power BI imposes a 1GB limit per semantic model under a Power BI Pro license. Exceeding this limit will halt report refreshes in the Power BI Service. Best practices for Power BI data modelling address all activities in Power Query and Relationship View, covering transformation steps and table relationships.

1. Transform Data Close To Source: The principle of data transformation states that changes should occur as upstream as feasible and downstream as necessary. Ideally, perform data transformations directly in your SQL queries before loading them into Power Query. If SQL isn't an option, focus on manipulating data in Power Query instead of relying on DAX functions. For those lacking SQL expertise, query folding in Power BI can be beneficial, which we will elaborate on later.

2. Avoid Many-to-Many Relationships: There are compelling reasons to steer clear of many-to-many relationships. From a user perspective, these relationships can be confusing, causing unexpected results with additional filters on reports. They also degrade performance, leading to longer loading times for graphs. You can verify this by employing a performance analyser to compare loading speeds for several one-to-many relationships versus a many-to-many relationship. Microsoft advocates for adopting a star schema instead of many-to-many relationships between two fact tables to ensure that all relationships in your data model adhere to a one-to-many configuration.

3. Bi-Directional Cardinality Relationships: Bi-directional cardinality often arises in many-to-many and one-to-one relationships. You can also set your cardinality to "both ways" manually in one-to-many relationships. However, this can introduce slower performance and confusing filter interactions for users, particularly when multiple relationships exhibit bi-directional cardinality in your model. For one-to-one relationships, consider using a "merge" function in Power Query to consolidate all necessary columns into a single table. Developers might employ bi-directional relationships to enable a single slicer affecting all visuals. Instead, apply distinct visual-level filters on each graph to obviate the need for a universal filter column.

4. Choose Appropriate Data Types: Selecting suitable data types for your columns can drastically reduce memory usage and bolster performance. Power BI usually auto-detects data types, but it’s essential to verify and adjust them as necessary. For instance, changing the data type from Date/Time to Date can significantly improve performance, as the former creates a small calendar table while the latter does not. Text columns typically consume more memory than numeric ones, so opt for numbers whenever your data model permits.

5. Remove Unnecessary Columns: A common mistake among newcomers to Power BI is retaining numerous columns in their data model, thinking they might need them later. This practice can considerably hinder the data model’s performance. Ideally, eliminate any columns not used in reports during the Power Query step before loading data. This streamlines the data Power BI must process and speeds up visual load times. Pay extra attention to text columns with lengthy values, as they consume excess memory.

6. Use Query Folding For SQL-Based Data Sources: Query folding is a performance enhancement method that allows Power BI to push data transformation steps back to the data source. When successful, Power BI issues a modified query to the source, which performs necessary transformations before returning results. It applies to most relational databases.
   
   To determine if your data source supports query folding, follow these steps:

• Navigate to the Power Query editor and select a query in the actions tab.

• If the View Native Query option is available, query folding is possible.

For illustration, suppose you need to filter for entries where YearOpened exceeds 1990, and NumberEmployees is greater than 10. You can apply these filters in Power Query and validate support for query folding, leading to improved query performance.

1. Avoid Loading Unnecessary Tables: Disabling the loading of tables not intended for use as dimensions or facts can optimise your reports. Particularly for tables involved in merging or appending actions, aim to disable their load. Consider three tables that you want to combine into one. You should avoid loading the individual tables to prevent data duplication; instead, load only the final appended table.

2. Avoid Direct Query: Using Direct Query should generally be avoided for several reasons. It limits the ability to perform many Power Query functions, leaving only basic data manipulations available. Graphs also tend to load more slowly because every filter change or new page visit necessitates pulling fresh data from the source. Conversely, using Import mode allows for data extraction during refreshes, enabling caching. If Direct Query is necessary for some tables, consider setting Import mode for others.

3. Aggregate Data: Aggregating your data, such as summarising it by product or customer, can reduce the number of rows and overall table size. Utilise the Group By feature in Power Query to condense numerical and categorical columns efficiently. This step plays a significant role in Power BI performance optimisation.

Power BI Performance Best Practices

While data model optimisation is crucial for enhancing Power BI performance, there are additional best practices to consider post-data loading.

1. Reduce Number of Graphs: A page crowded with multiple graphs can take longer to load. Look for ways to consolidate visuals; for instance, group several card visuals into a single table.

2. Use Less Slicers: Each slicer generates two queries, one for fetching data and another for filtered results, complicating data operations. Delete unnecessary slicers that do not add value to your analysis.

3. Avoid Custom Visuals: Microsoft’s built-in visuals are optimised for performance and generally load faster than custom ones from the marketplace. Whenever possible, create your desired designs using native visuals, even if it requires more complex DAX expressions.

4. Optimise Your DAX: Running a performance analyser can show how long your DAX queries take. If execution times are excessive relative to others, optimisation should be considered. A key tip is to avoid iterator formulas such as SUMX, AVERAGEX, and COUNTX, as these calculate results on a row-by-row basis, which is inefficient. If iterators must be used, apply them to the smallest tables possible and steer clear of nested iterators. Utilising variables in your DAX can also enhance performance.

5. Use More Powerful Hardware: The speed at which data can be extracted from a SQL database via Direct Query relies heavily on the database's power, measured by DTU or vCore. If feasible, upgrade your database. Hardware specs also play a significant role when utilising a data gateway; better CPU and memory can expedite dataset refreshes.

6. Adjust Data Gateway Settings: Optimising gateway settings can enhance data loading speed. By default, data is first retrieved from the source, then processed and compressed into a Power BI model. Enabling the Stream Before Request Completes command can expedite this process. Consider excluding certain folders from antivirus scans to further promote efficiency.

Power BI Design Best Practices

The best practices for Power BI design prioritise user experience and clarity. Key principles include layout planning, selecting optimal graphs, and adhering to established data visualisation guidelines.

1. Follow Data Visualisation Principles: Understanding the Gestalt principles of visualisation-proximity, similarity, continuity, closure, enclosure, symmetry, and figure-ground is vital for creating intuitive reports.

2. Select Appropriate Graphs: Utilising suitable graphs simplifies analysis and clarifies insights. Consult available guides for selecting the best visuals for your Power BI reports.

3. Use F-Shaped Layout: The F-shaped layout principle supports the idea that users read Power BI dashboards similarly to texts. Therefore, place critical visuals, like summary cards, at the top left and detailed tables in the bottom right for optimal data discovery.

4. Reduce the Amount of Visual Noise: Formatting elements that provide no value can become visual noise, causing distraction. Simplify your graphs by removing unnecessary axis names and reducing clutter in titles and text alignment.

5. Make Use Of White Space: Avoid cramming visuals together; instead, utilise white space to enhance readability and comprehension. This technique is akin to using pauses while speaking to give your audience time to absorb information.

6. Choose Colours Strategically: Our brains associate colours with emotions and statuses-green often means positive and red negative. Use colour strategically in your reports to help viewers quickly grasp the implications of the data.

Power BI Security Best Practices

1. Use Row-Level Security: Row-level security enables reports to display data relevant to a user's role. For example, different teams can view data specific to their regions while corporate teams have access to all data.

2. Assign Appropriate Roles In Workspaces: Adhere to the principle of least privilege, granting users the minimum access necessary for their tasks. This practice protects sensitive data assets from unauthorised sharing.

3. Assign Sensitivity Labels: Workspace admins can allocate sensitivity labels to datasets and dashboards, guiding users in understanding which assets are confidential and should remain unshared.

4. Power BI Report Server for Sensitive Data: For on-premises sensitive data that shouldn't migrate to the cloud, consider utilising the Power BI Report Server, allowing for processed data on-site without needing a cloud environment.

5. Monitor User Activity: Power BI admins have access to reports detailing user interactions with dashboards, offering insights that can be crucial for spotting security breaches or irregular access patterns.