Rules for DAX Code Formatting

and in this article you will see a set of rules for DAX code formatting that can be used as a starting point to define your personal style.

UPDATE: you can format your DAX code following the rules in this article at www.daxformatter.com

The goal of this set of rules is to improve readability of a DAX expression. In order to do that, an expression is oftentimes split in several rows and this does not match very well with the user interface of measure grid in PowerPivot and SSDT Tabular editor. At the moment of writing, DAX Editor already support this code formatting style (use the Visual Studio shortcuts CTRL+K, CTRL+D to format the entire DAX document and CTRL+K, CTRL+F to format the current selection) and I hope that DAX Studio will support it soon in a future release.

This article will be very short showing the code formatting rules by using an example and the set of rules. Based on the feedback, I will keep this content updated to reflect the code standard we want to use in future article and books.

This is an example of a formatted DAX expression:

CALCULATE (
    SUMX (
        Orders,
        Orders[Amount]
    ),
    FILTER (
        ALL ( Customers ),
        CALCULATE (
            COUNTROWS ( Sales ),
            ALL ( Calendar[Date] ) 
        ) > 42 + 8 – 25 * ( 3 - 1 ) 
            + 2 – 1 + 2 – 1 
            + CALCULATE ( 
                  2 + 2 – 2 
                  + 2 - 2 
              ) 
            – CALCULATE ( 4 )
    )
)

The following is the set of rules:

• Never use shortened CALCULATE syntax
• It means don’t use [measure](filter) but CALCULATE( [measure], filter ) instead – sorry Marius)
• Always put a space before parenthesis ‘(‘ and ‘)’
• Always put a space before any operand and operator in an expression
• If an expression has to be split in more rows, the operator is the first character in a new line
• A function call in an expression splitted in more rows has to be always in a new row, preceded by an operator
• Never put a space between table name and column name
• Only use single quotes for table name if it is required
• So omit single quotes if table name has no spaces
• Never use table names for measures
• Always use table names for column reference
• Even when you define a calculated column within a table
• Always put a space before an argument, if it is in the same line
• Write a function inline only if it has a single argument that is not a function call
• Always put arguments on a new line if the function call has 2 or more arguments
• If the function is written on more lines:
• The opening parenthesis ‘(‘ is on the same line of the function call
• The arguments are in new lines, indented 4 spaces from the beginning of the function call
• The closing parenthesis is aligned with the beginning of the function call
• The comma separating two arguments is on the same line of the previous argument (no spaces before)
• Definition of calculated column/measure is in the row before, including the assignment
• Use ‘=’ to define a calculated column
• Use ‘:=’ to define a measure

Based on the last rule, calculated columns and measures are defined as follows:

CalculatedColumn = 
CALCULATE ( … )
 
Measure := 
CALCULATE ( … )

I would like to get your feedback. Remember, the goal is code readability and I know that many people would prefer different code style in order to simplify the editing of the query, but this is not the goal of this set of rules. As soon as we will have a customizable code formatter integrated in all DAX editors, everyone will be able to define its own style and I really look forward for that day. In the meantime, I hope this could be a good starting point.