Power BI Data Modeling Checklist - Best Practice

This blog post offers a checklist while developing a data model for self-service and power bi dashboards. Before we consider all modeling work completed. It's advisable to refer to the checklist for best practices and standard solution design approaches. A standardized Data Model helps improve consistency and Q&A will improve overall user experience.

 🔲 Remove the tables and columns which is unused. 

 🔲 Check data type in Query editor & Data Model data types  - Number should not be stored as a string. Mismatch in data type a common mistake. 

 🔲 Check the data model for Data categories, Default summarization, and formatting 

 🔲 Check the sorting of Data in the data model - e.g. Date sorting 

🔲 Define the relationship between tables

• Check for sets cardinality (1:*, 1:1, *:1)

• Check active/Inactive relationships

• Set filter direction to single

🔲 Verify relationships Direction - The Direction of a relationship affects how filters get applied through the model. Use bidirectional relationships minimally and with a specific purpose not as a default

🔲 Make sure to apply the date table to “Mark as Date Table” 

🔲 For Direct query and live connection, If the power BI report is slow on the desktop. It will definitely slow report in service for end users. Optimize data sources as needed for the direct query.

• Performance - apply the filter first in the source

Switched off interaction between visuals 

 🔲 Use the meaningful name of tables e.g - DimCustomers → Customers (Rule: Concise, consistent & Logical)

 🔲 Use the meaningful name of columns directly from the source e.g. SalesAmt → Sales Amount (Rule: Concise, consistent & Logical)

 🔲 Keys (Primary Key or business key) of the table - make it hidden so it will not be part of Q&A. e.g - CustomerKey 

 🔲 Add description to fields  - Field List > Properties > Description  

🔲 Avoid ambiguity of columns and measure names - don’t duplicate similar names 

🔲 Split the datetime to - Date and time separately (recommended for performance)

🔲 Push calculated columns and measure to source whenever possible.

🔲 Create synonyms for the Q&A experience

🔲 Create a separate table for calculated measures

🔲 Use Error Messaging in DAX - ERROR() function provides a description  

🔲 Consider using Divide() function for calculation which will eliminate null and divide by zero.

🔲 Add Developer comments to DAX and M scripts & format DAX code 

🔲 Create an organized star schema model diagram layouts