THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

Format your #dax code with DAX Formatter

When you write a DAX formula that is not trivial, you end up having a complex expression that is not really readable. I think that the same problem affect in part all Excel users, but in Excel you can easily split a formula in many steps, using different cells. This is not always possible in Tabular, because moving a sub-expression in a separate measure might change the semantic, and this operation is possible only for scalar values and not for function tables. So we need to write readable code.

One year ago, we published the Rules for DAX Code Formatting. Today, I’m prod to announce the availability of DAX Formatter, a free service that automatically format the code following these rules. We designed the service to be easy to use. Type the DAX expression (or paste it) and press Format. That’s it.

http://www.daxformatter.com/

For those of you reading offline or too lazy to click on a link, here is how it works. Suppose you have this DAX query (but you can format also expressions for measures and calculated columns):

EVALUATE ADDCOLUMNS(FILTER(CROSSJOIN(
SUMMARIZE(Customer,Customer[Customer],Customer[Customer.Key0]),
VALUES('Date'[Date.Key0])),NOT ISBLANK([Internet Sales Amount])),
"Sales",[Internet Sales Amount])
ORDER BY 'Date'[Date.Key0] DESC,Customer[Customer] ASC

You paste it into DAX Formatter and you obtain this result:

EVALUATE
ADDCOLUMNS (
    FILTER (
        CROSSJOIN (
            SUMMARIZE (
                Customer,
                Customer[Customer],
                Customer[Customer.Key0]
            ),
            VALUES ( 'Date'[Date.Key0] )
        ),
        NOT ISBLANK ( [Internet Sales Amount] )
    ),
    "Sales", [Internet Sales Amount]
)
ORDER BY 'Date'[Date.Key0] DESC, Customer[Customer] ASC

Which is also more readable in the web site thanks to the layout available (you can choose between dark and light color scheme):

SNAGHTML609884

 

SNAGHTML60d8e8

And if you use non-US locale, just select the separators for you.
Please, Submit any bug using the Bug Report button.
Currently you can format any DAX query and expression in the form:

  • EVALUATE …
  • Table[measure] := …
  • [measure] := …
  • Table[column] = …
  • = …
  • measure := …

Feedback are welcome – enjoy!

Published Friday, December 13, 2013 12:44 PM by Marco Russo (SQLBI)
Filed under: , , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Oxenskiold said:

I can't believe my luck.

I'm preparing a text on 'Extended tables in DAX. I have 23 DAX formulas that I have (had) to copy from EXCEL's DAX editor - format with monospaced characters, parentheses and parameters line for line. I was really not looking forward to doing that. Well I guess I don't have to worry about that anymore.

This DAX formula is formatted with your new tool:

aMeasureOfChristmas :=

CALCULATE (

   SUM ( FactGreetings[Christmas] ),

   DimTheDaxPeople,

   DimCompanies[Name] = "SQLBI",

   FactGreetings[Christmas] = "Merry Christmas and thanks a lot"

)

PS: the FactGreetings [Christmas] field is a variant type field coming in Power Pivot 2014.

December 13, 2013 8:58 AM
 

Marco Russo (SQLBI) said:

Thank you very much and Merry Christmas! :)

December 13, 2013 9:59 AM
 

John Legelis said:

When Oh When will they add 'comment' syntax (eg.. //...  or /*...*/to DAX?   Seems like such a simple yet powerful request!

January 7, 2014 8:16 PM
 

Avichal said:

Marco, thanks for a very useful tool. I second John's comment that there should be a way to add comments in DAX. Hope that's coming soon from Microsoft!

January 9, 2014 2:54 PM
 

Marco Russo (SQLBI) said:

I know that comments are useful.

The implementation issue is the following: DAX Formatter parse the syntax and regenerate the expression in a formatted way. This approach is different than trying to move the text. For example, we transform the syntax [Measure]( filter ) into CALCULATE ( [Measure], filter ).

We have to evaluate if it's possible to include the comments as part of the syntax tree that is kept after the formatting - but comments between the formatted code are hard to manage in any case, at least with the current architecture we have.

I imagine you'd like a comment in any part of the expression (i.e. after an argument of a function call), but let me know if you think a reduced functionality (such as comments only before/after an expression) would be useful for DAX Formatter.

Thanks for the feedback!

Marco

January 10, 2014 11:35 AM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement