POWER BI >> HOW TO FORMAT ALL DAX MEASURE AT ONCE

We all write our codes in a proper format so that readability and understanding of codes become. While working with POWER BI, we use DAX Expressions to write our measures and calculated columns. We know that formatting is not required in DAX. It’s normal to see DAX expressions that span over 10 to 20 -lines or more, depending on the complexity and requirements. Sometimes we ignore DAX format as it is time-consuming, and we need to press ALT+ENTER for the new line, which is out of habit for all of us. There is too much ambiguity, and a lack of official specification makes it even harder to comprehend. Whenever I am working with Power BI, I use the following technique to measure it.

So to format our DAX, we use DAX FORMATTER. The best thing! You need to copy your DAX expression, click the FORMAT button on the website, and the page refreshes, showing a nice and clean formatted version of your DAX, which you can then copy and paste in the Power BI.

But what when you have 50 to 60 or even more measures and calculated columns in your model? Every time going to and fro DAX FORMATTER to Power BI is quite annoying and time-consuming.

Here is how the expression looks like if you do not format it in some way:

Total Sales Category =VAR Parts = CALCULATE ([Sales Item Quantity],ALLSELECTED(Details))RETURN IF (ISINSCOPE ( ‘Sales ‘[Subcategory] ) && ISBLANK ( VALUES ( ‘Sales ‘[Subcategory] ) ),BLANK (),IF(ISBLANK(Parts),0,Parts))

And below code is the formatted one.

Total Sales Category =

VAR Parts =

CALCULATE ( [Sales Item Quantity], ALLSELECTED ( Details ) )

RETURN

IF (

ISINSCOPE ( ‘Sales ‘[Subcategory] )

&& ISBLANK ( VALUES ( ‘Sales ‘[Subcategory] ) ),

BLANK (),

IF ( ISBLANK ( Parts ), 0, Parts )

)

We can see the formatted Dax is easier to understand. Most important, it is easier to follow the blocks that arise naturally from indenting lines and the complete flow of execution.

Hence you know how vital formatting of DAX is, but also, you don’t want to go through time-consuming approaches. So here is the solution. With few steps, you can format all your DAX measures in one go.

So let’s start with how to achieve it.

Step 1.

Download and install the updated version of Tabular Editor on your machine.

After that, you will be able to see the external tool tab on your PowerBI Desktop.

And Go to your file and launch Tabular Editor.

Step 2.

Go to the Advanced Scripting, next to Expression Editor; the Tabular editor will take each line and format it. Use the below code to see it in action.

foreach (var m in Model.AllMeasures)

{

m.Expression = FormatDax(m.Expression);

}

Step 3.

Execute the code by clicking on the green button or press F5. Wait for few mins until you see “Script Executed successfully” on the bottom right corner of the Tabular editor, as shown below. With that, you can also see the number of changes made. Like here in my model, I had only one measure, so “1 model change” is written.

That’s it, and you are good to go. Don’t forget to save your work.

Step 4.

Close the tabular editor and check your measures on the Power BI desktop. Hurrah, all your measures are Formatted. That’s it! You are done!

So we have formatted all DAX measures in a much simpler way. But I would like to mention that only Measures are formatted and not the calculated Columns with this method.

Please let me know if you have any queries or suggestions.

Happy DAX-ting!