Power BI Best Practices for DAX, Formatting and Maintenance

Power BI Best Practice Rules

When it is to Power BI best practice rules, there are many to follow. And as told earlier in our last blog, we shall help you understand when and where these rules are supposed to be implemented. If you haven’t read part one on Power BI best practices, you can find it here, “Power BI Best practices for security, Performance and Design”. However, in this blog, we shall cover the remaining 5 categories.

This blog will cover the Power BI Best Practice rules for

  1. DAX Expressions
  2. Error Prevention
  3. Formatting
  4. Maintenance
  5. Naming Conventions

Without any delay, let’s start with the first one

Power BI Best Practice Rules for DAX Expressions

  • Use Selective Conditions on Inner Functions
    Using the nested function will evaluate the inner function first. So, for DAX optimization as well as good performance, nest the most selective condition to limit data as early as possible.
  • Use Error Messaging and Avoid using the IFERROR function
    The ERROR () function handles the errors. Thus, helps users from getting annoyed.
  • Use the Divide () Function instead of Colum A/Column B
  • Use Variables as a Common Practice
    If you have a complicated DAX expression to get the result, it is advised to use various variables to accumulate the result and use it in the final result.
  • Column references should be fully qualified while Measure references should be unqualified
  • No two measures should have the same definition and should not be direct references to other measures
  • For virtual relationships, Use the TREATAS function instead of INTERSECT

PBI rules for Error Prevention

  • Calculated columns should have an expression
  • Data columns should have a source column

What are the best Power BI rules for Formatting?

  • Include data category for columns
  • Capitalize the first letter of objects
  • Hide foreign keys and Mark primary keys
  • Numeric columns do not need summarization
  • Hide fact table columns
  • Month name in string order should be correctly sorted
  • Do not start or end Objects with a space
  • Format
    • “Percentages” with thousands separators and one decimal
    • “Whole numbers” with thousands separators and no decimals
    • Flag columns as Yes/No value strings
    • The “Date” column as a date data type
    • “Month” columns as a string data type
    • “Measures” as a numeric data type
    • “Relationship columns” as an integer data type

Maintenance

  • Delete irrelevant columns and measures
  • Get rid of data sources with no reference to any partitions
  • Eliminate roles with no members
  • Tables should have relationships
  • Do not provide descriptions of Objects

Naming Conventions

  • Partition name should match table name for single partition tables
  • Use CamelCase for hidden columns
  • Do not use special characters in Object names

Summary

To conclude, we’ve wrapped the Power BI best practice rules for optimized DAX expressions, error prevention, formatting, maintenance and naming conventions. Also, we covered them for Security, Performance and Design too.

From part one to this part of the blog, we make an effort to cover up as much as possible with explanations where needed. So, at this point, we hope you get benefited from them by implementing them and getting to know the difference.

Leave a comment