With an increase in adoption of Power BI comes challenges to provide the right approach and design for an optimal dashboarding solution. Power BI best practices will help us to overcome those challenges and do it right the first time rather than a retrospective approach to course correct on the design choices.

In this article, we have attempted to classify the best practices based on the Power BI workflow – data loading, modeling and creating visuals. This article will explain how to address the challenges linked to the below:

• Data Connection
• Data Modelling
• Data Analysis Expressions (DAX)
• Data Visualization
• Gateway Connection

Understanding these best practices and incorporating them into your routine will help improve the performance and design of reports and dashboards.

Trust this article helps. Please provide your comments and feedback on whether this article was useful for you.

Data Connection Best Practices:

Getting organized, before loading the data into Power BI

• Define clear and meaningful table names (no spaces between them, e.g., AW_Products_Lookup in the below table) from the starting, define a meaningful name for your table as soon as loaded into power BI desktop using the “GET DATA” Button use click Transform Data to edit the table name in the query editor, Reason this best practice is, updatin them later will be difficult, especially if we referenced them in many places

• Maintain a file/folder structure that makes sense from the beginning, this helps to change the file/folder name in the Data source settings if the file/folder name changed

Only load data you need!

• Don’t load all data, while loading a large table, for example, if we are working with product level or daily level data there is no necessity to load hourly data, this will slow down the performance of the report, remove all those columns that seem unwanted

Disabling the report refresh for static data sources

• Turn off the report refresh for the sources that are don’t update frequently (especially for lookup tables), enable refresh only for tables that will be changing. In the power query editor, Right-click on the table name in the Queries pane and uncheck the “Include in report refresh” option to turn off the report refresh, this best practice improves the performance and speed of the report after publishing

Data Modelling Best Practices:

Build a normalized data model from the start

• Use relationships (one to many) instead of merged tables, the table should belong & narrow, not short and wide because Power BI fetch the values column-wise not row-wise
• Each table should serve a distinct and sole purpose like below, Calendar, Customer Details, Product categories, Returns, Sales, etc., this reduces the ambiguity and redundant and values

Use downstream flow

• Always place the lookup tables above and the data tables bottom, like downstream flow

Avoid complex filters

• Don’t use complex 2-way filters unless it is necessary, use 1-way filters, because 2- way filters create complexity while visualizing the data

Hiding foreign keys from the report view

• Hide the foreign keys of data tables from report view, this will prevent the users to access valid fields

Using calculated measures

• Use calculated measures instead of using calculated columns, this best practice help us to increase the report processing speed

Categorize the data

• Categorize the data as High Business Importance (HBI), Medium Business Importance (MBI) and Low Business Importance (LBI), this best practice will help us to place the most important things on the top and left side of the report pages

Eliminate redundant columns

• Data tables should only contain quantitative values and foreign keys, eliminate extra columns that are not serving its purpose

Data Analysis Expressions (DAX) Best Practices:

Minimizing the iterator functions

• Avoid using iterator functions such as (FILTER, SUMX, etc.) because these iterator functions will cycle through each row in the table, will take more time and processing power, avoiding this will improve loading speed and time

Data Visualization Best Practices:

Strive for clarity & simplicity

• Our aim should be maximizing the impact and minimizing the noise in the report
• Reducing the number of visuals per page is the best practice to improve the report performance, it’s good to use 8 – 10 visuals per page of our power BI report

Placing first things first

• Most of the people read from left to right and Top to bottom, so it will be good to place the most important visuals first on the left and top of the visuals

Avoiding Hierarchical filters

• Filters are good, but if it is affecting the speed of the report, it should be removed, avoiding the hierarchical filters will speed up the report performance

Avoiding unwanted interactions between the visuals

• Interactions between each visual should be optimized, unnecessary interactions between the visuals must be removed

Create a narrative report

• Visuals should tell a story, don’t just use the charts and graphs, use bookmarks, filters for creating a narrative report

Make use of Row Level Security (RLS)

• Use Row Level Security for allocating separate roles for the users, this best practice will reduce the conjunction of users to particular roles in the report

Gateway Connection Best Practices:

Using on-premises data gateway

• After publishing our report use on-premises data Gateway instead of Personal Gateway while connecting data sources from personal computer to Power BI service

Use separate gateway based on their purpose

• Use separate Gateways for Live connection and scheduled refresh using the same gateway will reduce the performance of the report, using a separate gateway is best practice