Power BI Best Practices - Part Two

Overview

In this article, we will talk about best practices to be considered while we are working with Power BI report development. I have divided the best practices into the following three parts. This is the second article in this series.

Please visit my other articles of this series using the following URL.

• Power BI Best Practices – Part 1
• Power BI Best Practices – Part 3

During the entire series of the best practices, we have covered the following concepts.

• Best Practices for Power BI (. PBIX) file preparation (Part – 1)
• Best Practices for Power BI (. PBIX) Properties (Part – 1)
• Best Practices to use Query Editors (Part – 1)
• Best Practices for Tables and Relationship (Part – 2)
• Best Practices for creating Calculated Columns, measures, and Fields (Part – 2)
• Best Practices for Data Analytical Expressions (Part – 2)
• How to make Documentation within PBIX file (Part – 3)
• Best Practices for Power BI Report Design (Part – 3)

In this article, we will talk about the following three concepts for the best practices.

• Best Practices for Tables and Relationship
• Best Practices for creating Calculated Columns, Measures, and Fields
• Best Practices for Data Analytical Expressions

Let’s get started!

Best Practices to use Query Editors

• Choose Proper Data Connectivity Mode
  
• Before we start to prepare the report, we need to think about Import and DirectQuery Mode to load the data.
• Choose the DirectQuery Mode if you want live data connection with your database. Choose the Import mode if you need to refresh on daily basis.
  
• Import the Minimum number of Columns
  
• Import only the columns which are needed.
• Remove the unnecessary columns which are not used to represent the graphs.
  
• Retrieve Source Data from a Database View
  
• It’s also recommended to consume the data from Database view instead of a table because Views can have a clustered index assigned and, when they do, they'll store temporary results that can speed up resulting queries.

Best Practices for creating Calculated Columns, measures, and Fields

• Use Unique Field Name across the entire Dataset.
  
• Use unique dataset field names for the dataset.
  
• Hide fields which are not utilized for reporting.
  
• Hide the fields which are not used directly but used only as a part of the calculation.
  
• Use Friendly Names for Fields.
  
• Use friendly names for the field names which is to be easily understable to the end-user.
  
• Format all numbers and date fields
  
• If we format the numeric and date fields, it provides us some OOTB summarization which is very useful to display the data.
• If we convert the field as Date, it will automatically provide us Date Hierarchy.
  
• Specify Sorting for Columns
  
• Use sort order column, if we need default sort on some specific columns.
  
• Create Useful Calculated Columns and Measures
  
• Whenever we need to perform any calculation based on already available fields, we can create calculated columns or measures as per the requirement.
  
• Set the Data Category
  
• This is similar to “Format all number and Date fields”. For example, suppose, we have fields like city, state, and zip, we need to set the data categories, so that Geo Coding data can be displayed properly.

Best Practices for Data Analytical Expressions

• Apply the Most Selective Condition on Inner Functions
  
• In DAX, if we have used the nested function, then it will evaluate inner function first.
• For performance optimization purposes, the most selective condition should typically be nested to limit data as early as possible.
  
• Use Error Messaging in DAX
  
• User ERROR() function to handle the errors.
• Error handling helps users from getting frustrated should something go wrong with the report display.
  
• Consider Using the Divide() Function
  
• We recommend to use Divide() function instead of Colum A/Column B in any future report.
  
• Use Variables as a Common Practice
  
• If we have a complex DAX expression to derive the end result, we recommend using multiple variables to store the result and use it in the final result.
• Use of variables should be done in an effective manner.

Conclusion

This is how we recommend the best practice to prepare the PowerBI report. Hope you love this article. Stay connected with me for more amazing articles!