Writing good DAX

While the term good can be subjective, writing good DAX code is very much like writing code in other programming languages; the code should be readable and easy to understand. In this recipe, we will learn how to properly format DAX as well as how to use comments.

Getting ready

To prepare for this recipe, perform the following steps:

1. Open Power BI Desktop.
2. Create a table using the following formula:

R02_Table = GENERATESERIES(1,30,1)

3. Create a column in that table using the following formula:

Column = ROUNDUP('R02_Table'[Value]/11,0)

How to do it...

Let's assume that you have a measure formula that looks something like the following:

Bad DAX = SUMX(FILTER(SUMMARIZE('R02_Table',[Column],"Value",SUM([Value])),[Column]=2||[Column]=3),[Value])

While the preceding formula is syntactically correct and will work, it is difficult to read and understand the intent of the calculation. There are a number of best practices that can be followed to make the code more readable, including the following:

1. Use single-line comments (//) or comment blocks (/*...*/) to document the calculation being made.
2. Use Alt+Enter and Tab to separate and indent nested functions and multiple arguments.

3. Always use the full, canonical syntax when referring to a column, enclosing the table name portion in single quotes.
4. Use spaces to provide visual separation between elements, including parentheses and operators.
5. When creating data elements within a calculation, such as columns, clearly name these elements distinctly in order to avoid confusion.

Following these simple best practices, we can rewrite the measure as follows:

Good DAX = 
/* Creates a filtered sum for a certain business purpose
 * 
 * Gregory J. Deckler
 * [email protected]
 * 10/7/2019
 */
SUMX(
    FILTER(
        SUMMARIZE(
            'R02_Table',
            'R02_Table'[Column],
            "__Value",
            SUM( 'R02_Table'[Value] )
        ) ,                               // End SUMMARIZE
        'R02_Table'[Column] = 2
        ||
        'R02_Table'[Column] = 3
    ) ,                                   // End FILTER
    [__Value]
)                                         // End SUMX

It is best practice to use single quotes for referencing table names. The issue is consistency. If you have spaces in your table names, then you need single quotes. If you do not, you can get away with not having single quotes, but it will burn you eventually. So, it is better to always use single quotes, and this has the added benefit of you always knowing that these are tables being referenced when you see single quotes.

How it works...

The comment block at the top of the function provides useful information regarding the purpose of the measure as well as a description regarding its operation. In addition, this comment block includes information about the author, including contact information and when the calculation was created. This information assists someone else reviewing the code or the author if revisiting the code at a later date. A space and asterisk have been added to lines within the comment block to visually cue the reader that the entire comment section belongs together. In addition to the comment block, inline comments have been used to call out where functions end. This makes it much easier to read the code instead of hunting for beginning and end parentheses.

Each function has been placed on its own, separate line by using the Alt+Enter key combination. In addition, each argument for each function is also on its own line, except for the SUM function, since this only has a single argument. The Tab key has been used to indent the nested functions, clearly denoting the nesting hierarchy. In addition, the Tab key has been used to indent function arguments underneath each function, visually keeping coding elements together.

The full, canonical name of columns has been used in order to remove any ambiguity and improve readability. Someone looking at the code immediately understands what column and table is being referenced in the code. These table names have been prefixed and suffixed with single quotes. While not required for table names without spaces, for consistency, they should always be used.

Spaces inserted after beginning parentheses and before end parentheses, as well as before and after the equals sign, provide visual separation between elements and make things easier to read.

Finally, the creation of the column in the SUMMARIZE statement has been created with the name prefixed by two underscore characters, unlike the original formula, where this column is the same name as a column from the original table. While DAX can generally figure out which column is being referenced, having duplicate names is confusing for the reviewer and can create actual confusion and problems in complex DAX formulas.

There's more...

Inline or single-line comments can also be executed by using -- instead of // at the beginning of the comment. You can also use Ctrl+/ to automatically comment out or comment in a line using the // style of comment.

Instead of using Alt+Enter and Tab, you can use Shift+Enter to move to a new line and indent all at once. In addition, you can use Ctrl+] to indent and Ctrl+[ to outdent instead of using Tab and Shift+Tab.

See also

For more details regarding this recipe, refer to the following links:

• Rules for DAX formatting: https://www.sqlbi.com/articles/rules-for-dax-code-formatting/
• DAX Formatter: https://www.daxformatter.com/
• DAX formula bar keyboard shortcuts in Power BI Desktop: https://xxlbi.com/blog/dax-formula-bar-keyboard-shortcuts-in-power-bi-desktop/
• The Using variables recipe