While unformatted DAX formulas may not affect the calculation speed, they can take a lot of time to read, and they can even hide errors just because the errors are hard to spot. Despite there being several ways to format your DAX code, I still see a lot of formulas that aren’t formatted.
For example, can you tell what this formula is doing?
Example bad = CALCULATE(SUM(Sales[FullAmount]) , FILTER(Sales, True = (CONTAINS(VALUES('Item'[Full or Discounted]), 'Item'[Full or Discounted], "Full") && COUNTROWS(VALUES('Item'[Full or Discounted])) = 1))) + CALCULATE(SUM(Sales[FullAmount]) , FILTER(Sales,True = (CONTAINS(VALUES('Item'[Full or Discounted]), 'Item'[Full or Discounted], "Discounted") && COUNTROWS(VALUES('Item'[Full or Discounted])) = 1)))
In its current state, it’s difficult to read the formula. Once you format the formula, you’ll see other issues immediately, one of them being that most constructs aren’t needed, and there’s a mistake in the formula.
Can you spot what the issue is?
The formula above is a real formula written by someone else — I only changed table and column names, and I kept the rest as is. There really was a mistake, which was hard do spot, because the formula was so difficult to read!
How can you format you code?
Your own formatting
If you’re disciplined enough, you can format your formulas yourself, which requires no tools at all. The most important thing about it is to be consistent. If you’d like to know which key combinations you can use to add/reduce indendation, comment, add line breaks, etc. you can read my other blog post: DAX formula bar keyboard shortcuts in Power BI Desktop.
DAX Formatter
Before I got into the habit of formatting my formulas myself, I used DAX Formatter a lot. It’s a web-based tool, and it’s very easy to use — you just paste your formula and select Format. You’ve got the option to change some settings too, like spacing and separators.
You can then copy by selecting the Copy button, then paste your formula back to Power BI. DAX Formatter is good when you need to format one formula at a time.
DAX Studio
In DAX Studio, there’s a button to format your query, and it works for individual formulas too. The keyboard shortcut is F6:
Again, this is good when you only need to format one formula you’re working on.
Tabular Editor
In Tabular Editor (2 and 3) you can also format code by using the same DAX Formatter button, and the keyboard shortcut is also F6:
While you can format individual formulas, you can run an advanced script in Tabular Editor to format all your measures. See Tabular Editor documentation for more details: Useful script snippets | Tabular Editor Documentation.
Bravo
One of the newest external tools, Bravo, also allows you to format your code, and you can also format all your DAX measures at once!
This tool probably offers the easiest way to format your formulas in bulk.
Others
I’m sure there are other ways to format your DAX formulas… There used to be a Power BI external tool done in Python by Davis Zhang, although I don’t see it anymore, and there could be others that I may have missed. Let me know in the comments if there’s another good way to format your DAX!
Update 27 April 2022: Sandeep Pawar reminded me on Twitter that Phil Seamark built his tool as well: DAX Formatter tool for Power BI Desktop – Phil Seamark on DAX.
Happy formatting!