Formatting DAX Code In Power BI

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.

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.

Personally, I like to keep this relatively simple here and I don’t try to over complicate it.

 

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’ )

Another example

formatting 2

Few Simple Ideas

  1. Always indent on a new row if you are referencing a new function
  2. Place spaces after and before you open and close brackets
  3. Place spaces around operators like ‘+’, ‘-‘, ‘=’

 

That’s it really. Don’t overcomplicate it, that’s my big piece of advice here.

Few other things to think about.

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.

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

 

***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – 60 Page DAX Reference Guide Download
FREE – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online

 

***Learn more about Enterprise DNA Membership***membership banner 3

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s