Formatting DAX Code

No comments

Effort here will pay dividends later

Placing effort into formatting your DAX code will make a huge difference as you build out your data models in Power BI. DAX, at times is a confusing language as it is, so as soon as you get into the slightly complex territory unformatted DAX code becomes almost impossible to read and understand.

I recommend taking some time out early in your DAX journey to quickly learn and then practice these rules. Make it a habit to just write your code like this the first time. The formatting rules are promoted by SQLBI who have develop this seriously helpful tool called the DAX formatter.

If you find yourself battling to understand someone else’s DAX code or even your own after you have left a Power BI model for a while, then first step would be to format all of the code so that you can quickly understand what the original creator was up to. You’ll likely find it’s easier to spot the common patterns in DAX itself if you use best practice or get the DAX formatter to do it for you.

Key Rules of DAX Formatting

An example:

Total Bookings =
IF (
    ISFILTERED ( ‘Date Ranges'[Date Range] ),
    CALCULATE (
        COUNTROWS ( ‘Booking Data’ ),
        FILTER (
            ‘Date Table’,
            ‘Date Table'[Days Prior to Today] >= [Starting Date]
                && ‘Date Table'[Days Prior to Today] <= [Ending Date]
        )
    ),
    COUNTROWS ( ‘Booking Data’ )
)

Functional Calls

  1. Any new functional call is placed on a new line
  2. The first bracket in a functional call is after the function on the same line
  3. If there is only one argument, that argument remains on the same line
  4. Place arguments on a new line if there is more than one
  5. The bracket to close the function should align with the initial function call
  6. Comma’s separating arguments should be on the same line as the previous argument
  7. Function arguments should be indented 4 spaces from the original function call
  8. If a functional call is spread over more than one line, operators should always be the first character

 

Spacing

  1. Always place a space after brackets, as in ‘(‘ and ‘)’
  2. Always place a space after any operators like ‘+’, ‘-‘, ‘=’

 

Never

  1. Use a table name as a name for a measure
  2. Include a column name without referencing the table where it comes from
  3. Place a space between the table name and column name

 

Get into good habits early

In my experience, spend a few hours building out a Power BI model, making sure that you follow these rules while you write any formula. After about 12-15 formulas you’ll basically have it nailed. All it takes is a bit of repetition. You can always check to make sure you’re on the right track, by copying your formula into the dax formatter by SQLBI and you’ll get immediate feedback on if you have followed the right formatting.

Remember that this is not THE ONLY way to format DAX. It is just the recommended one by the team at SQLBI (who’ve written the Bible on DAX). For me it’s made a massive difference as I’ve built out reports and models with Power BI. After coming back to a model after some time, it is so much easier to scan down a properly formatted formula and immediately know what is going on when a result is returned.

I highly recommend getting into these habits early on. It makes a huge difference.

DAX Formatter by SQLBI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s