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 the 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 practices 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
Few Simple Ideas
- Always indent on a new row if you are referencing a new function
- Place spaces after and before you open and close brackets
- 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
- Use a table name as a name for a measure
- Include a column name without referencing the table where it comes from
- 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.
Cheers!
Sam
***** Related Links*****
Format Your DAX Formulas Easily With Our New DAX Clean Up Tool
Converting A YYWWD Code Into A Date Value In Power BI
Power BI DAX Formula Technique: Showcasing Growth Performance Effectively
