A Best Practice Use of Formatting, Variables and Comments in Power BI DAX Expressions

Colin Maitland, 16 September 2019

In this article I demonstrate a best practice for the writing of a Data Analysis Expression (DAX) in Power BI regarding the use of formatting, variables and comments.

The formula used for my demonstration adds a calculated field named Is Supporter to a table named Supporter. This is a table of merged Individuals and Organisations retrieved from Dynamics 365. Not all Individuals or Organisations are Supporters, and not all are current Supporters. However, all Individuals and Organisation need to be included in the Supporter table — hence the need to identify which are Supporters; i.e. only those where Supporter Type is Member or Donor, and Status Reason is Current.

The purpose of the Is Supporter flag field in this example, which contains only the values 0 or 1, rather than True or False, is to provide a field that can be used as a filter or as a value that can be aggregated.
The Is Supporter field can be used as a filter in a Power BI Visualisation, Page, Report or Measure.

Unlike the value True, the value 1 can be aggregated, e.g. “SUM ( Supporter[Is Supporter] )”. In addition the values 0 and 1 can be evaluated as if they were True or False; e.g. “IF ( Supporter[Is Supporter], …, … )” evaluates to True if the value is 1 and False if the value is 0.

The formula references two Microsoft Dynamics 365 Option Set values related to a custom Dynamics 365 Option Set named Supporter Type Code. The formula also references another flag field named Supporter[Is Status Reason Current].

Here is the formula as it might be initially written; without the use of formatting, variables or comments.

Here is the formula with the addition of formatting based on standard DAX formatting conventions.

Here is the formula with the addition of variables. As a result, a RETURN statement must also be used. However the names of the variable provide clarity and understanding. In my example, when naming variables, I am using an upper case “_VARIABLE_NAME” with underscores. You might consider the use of another variable naming convention such as “variableName”. As an aside, all variables in DAX are actually constants! Once set they cannot be changed.

Here is the formula with the addition of comments. Here, I have demonstrated three styles of comments.

The result is a formula that is easy to read, understand, use and maintain. The use of Formatting, Variables and Comments  is especially applicable for longer and more complex formulas.

Finally, and as an aside, the performance of the formula used in this example can be optimised by using OR instead of IN as shown here:

In addition, you might consider not using variables when the values they represent are only used once in the formula and the formula is very simple. However, comments should be used to indicate the meaning of the values used.