Top 20 Best Practices of Power BI

In today’s business intelligence world, Power BI has become a beloved among many. In this blog, I am going to cover Power BI best practices, that will assistance to you while developing the Power BI report.

1. Company Logo

Use the logo in their background.

2. Data Timestamp to show, when it was refreshed last

We can implement it, to show the end user, when the data was refreshed last.

3. Less use of scrollable Page

• End users always feel difficulty when the report has a scrollable page.
• We do not recommend making the page scrollable if not necessary.
• Instead of making page scrollable, use Bookmark and Selection Pane.
• Census dashboard doesn’t have a scrollable page which is good.

4. Use Basic Reporting Filter

Power BI provides the following type of filters.

• Visual Level
• Page Level
• Report Level

Use the filters wisely as per your requirement.

5. Pull data from views, not tables

Importing data from tables in a SQL Server, MY SQL or Oracle database creates strong dependencies between the physical data model and the reporting engine. Whenever table structures change, it’s best to pull relational data from views.

6. Filter before import

If you’re importing data into Power BI instead of a live connection, it’s best to limit the amount of processes that happen inside the tool. Power BI has a limit for the amount of data that’s allowed to be imported, so any steps to avoid reaching that limit will be a plus.

7. Narrow tables are faster than short and wide tables

If the performance is slowing down as you’re adding data, it’s mostly due to wide tables.  Power BI reacts much faster using narrow and long tables versus short and wide ones.

8. Remove unused fields

Whenever adding more and more data, you will notice the pbix file size increasing.  One of the best and quickest ways to reduce the pbix file is to remove any unused fields.

How: Click Edit Query > then select the table you want to remove the fields from > Click Choose Columns.

9. Label all of your steps

As you’re going through and modifying the imported data, Power BI creates a history that allows you to seamlessly go back and remove any changes that might break the datasets.  Furthermore, labelling each of these steps allows you to easily remember what each does.

10. Limit the visuals in dashboards and reports

The Microsoft Power BI performance best practices highlight that placing many visuals in a single report is responsible for it. This is what you need to do in order to limit the number of visuals in dashboards and reports:

• Limit to a minimum of eight widget visuals in every report page and keep the grids to a minimum of one in every page
• The pages should be limited to no more than 30 points (cards: 1, gauges: 2, charts: 3, maps: 3, grids: 5)
• Keep the tiles limited to no more than 10 per dashboard.

11. Remove unnecessary interactions between visuals

Do you know the secret of improving Power BI report performance? Here’s a clue! You can make that possible by removing unnecessary interactions between visuals. This is possible because of the reason that all visuals on a report can interact with one another by default. The interactivity should be controlled and modified for optimal performance.

Further, you can reduce the number of queries fired at the backend and improve report performance by disabling unwanted interactivity.

12. Enable Row-Level Security (RLS)

Power BI only imports the data that the user is authorized to view, with RLS that restricts user access to certain rows in a database depending on the characteristics of the user executing a query. But how to attain substantial performance gains? You can enable this by combining Power BI roles with roles in the backend. Moreover, you need to test all roles prior to rolling out to production.

13. Use Microsoft AppSource certified custom visuals

The Power BI certified custom visuals are verified by Microsoft to have robust as well as well-performing code. These AppSource visuals have passed rigorous quality testing and are the only custom visuals that can be viewed in Export to PowerPoint and email subscriptions.

14. Avoid using hierarchical filters

We recommend, not to use any hierarchical slicers in the report.
Currently, we have observed that no hierarchical slicers are used in the report. Experience an enhanced performance in Power BI by using multiple filters for the hierarchy.

15. Categorize the data for Power BI reports

One of the best practices in Power BI is to provide data categorization for the Power BI reports (HBI, MBI, LBI). The Power BI data classification enables you to raise user awareness about the security level that is required to be used. This also helps you to understand the way reports should be shared inside as well as outside the organization.

The categories can be listed as:

• HBI or High Business Impact data, that requires users to get a policy exception to share the data eternally.
• LBI or Low Business Impact as well as MBI or Medium Business Impact, that do not require any exceptions.

16. Use the On-premises data gateway

It is suggestible as well as one of the best practices to use on-premises data gateway instead of Personal Gateway for it takes data and imports it into Power BI. But why Enterprise Gateway? It is more efficient while you work with large databases as Enterprise Gateway imports nothing.

17. Use separate Power BI gateways for “Direct Query” and “Scheduled Refresh”

Using the same gateway for Scheduled Data Refresh and Live Connection slows down the Live Connection performance when the Scheduled Data Refresh is active. It is suggestible for you to create separate gateways for Live Connection and Scheduled Refresh to avoid such issues.

18. Test each custom visual on a report for ensuring fast report load time

The Power BI team doesn’t thoroughly test the custom visuals that are not certified. So, while handling large datasets or complex aggregations, the custom visuals might perform poorly. What should you do when the chosen visual performs poorly? You can overcome the issue by using an alternative visual. Ensure fast report load time by testing each custom visual on a report for performance.

19. Limit complicated measures and aggregations in data models

Increase the likelihood of improved performance by pushing calculated columns and measures closer to the source wherever possible. Moreover, you need to create calculated measures instead of calculated columns while using star schema in order to design data models.

20. Before planning any report development, we will consider the following points

• First, we think about the data in Grid
• We use proper suitable charts which represent the data in an effective manner.
• Use the proper colour scheme.
• Do not mess up, make it simple.
• Use simple titles and headers.

I hope this is helpful.