Skip to main content

Best practice rules to improve your model’s performance and design v1.1

Today we are releasing an updated version of the Best Practice Rules for Power BI and Analysis Services modeling. If you haven’t already, please make sure to read the original blog post on this topic.

In this version 1.1 release, we have updated existing rules to make them more effective as well as added new rules to capture additional scenarios which can be assessed for model improvement.

Before we get into the updates, let’s cover a several points.

  • In order to take advantage of all of the latest rule updates, make sure to download the latest version of Tabular Editor (2.16.1 as of this writing).
  • It should be noted that many of the rules have a description and link to a reference article which provide more context for the rule. When running these rules against your model it is critical to read the descriptions to understand the full context of the rules. For instructions on how to see these rule descriptions, see the ‘View Rule Descriptions’ section within the original article on this topic.
  • If you are interested in quantifying the savings gained by following these rules, be sure to check out this post.
  • Make sure to read this post so as not to miss out on some of the more advanced Best Practice Rules which leverage Vertipaq Analyzer data.
  • If you have any issues with these rules or have suggestions for new rules, please create an issue on the GitHub repository.
  • If these rules have helped you or your organization, we would love to hear your feedback. Email us at pbibestpractice@microsoft.com.

 

This new version of the rules can be downloaded here on GitHub.

Now let’s dig into the updates!

 

New Rules

  • [DAX Expressions] Filter column values with proper syntax

This rule scans for a poorly performing syntax in DAX when filtering on a column value within a CALCULATE or CALCULATETABLE statement. Make sure to see the rule description for the full rule context.

  • [DAX Expressions] Filter measure values by columns, not tables

This rule scans for a poorly performing syntax in DAX when filtering on a measure value within a CALCULATE or CALCULATETABLE statement. Make sure to see the rule description for the full rule context.

  • [DAX Expressions] Inactive relationships that are never activated

This rule finds relationships which are marked as inactive and are never ‘activated’ in a measure using the USERELATIONSHIP function. Such relationships are either not necessary or should be ‘activated’ using a measure as previously specified.

  • [Maintenance] Perspectives with no objects

This simple rule detects perspectives that contain no objects (i.e. tables, columns, measures or hierarchies).

  • [Maintenance] Calculation groups with no calculation items

This simple rule detects calculation groups which have no calculation items.

 

Modified rules

  • [Naming Conventions] Partition name should match table name for single partition tables

A ‘fix expression’ has been added to this rule so that the issue can be resolved by using the ‘Generate Fix Script’ or ‘Apply Fix’ functionality within Tabular Editor. For more information on this see the official documentation. Note that this specific fix requires Tabular Editor version 2.16.1 or higher.

  • [Error Prevention] Calculated columns must have an expression

This scope of this rule now encompasses not just Measures but also Calculated Columns and Calculation Items. As such, the rule name has been changed to ‘Expression-reliant objects must have an expression’.

  • [Maintenance] Objects with no description

The scope of this rule has narrowed down to only visible objects. As such, the rule name has changed to ‘Visible objects with no description’.

 

Removed rules

  • [DAX Expressions] No two measures should have the same definition

We decided to remove this rule as the rule itself runs slowly when analyzing large/complex models.

 

Conclusion

As of this newly released version (1.1), we now have 60 best practice rules to help guide you to a model with better performance and a more optimized design. We welcome all feedback regarding these rules and will continue to seek ways to refine the rules and make the process for improving a model even easier.