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

[Anonymous]

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.

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!