Help us to continue to improve the DAX Clean Up tool rules

Here are the current rules for the DAX clean up tool

Break after “=” if on line 1
Place a space after “(“
Place a space before “)”
Place a space after a “,”
Place a space before and after any operand and operator in an expression
Place a DAX function on a new indented line
After VAR name, always go to new indented line
RETURN should always be on it’s own line with a space above it
Any VAR should begin on a new line against the left margin
&& or || should be placed on the next row
Function parameters go to the new line based on line length

We’re open to continuous improvement on these rules. So please share below if you have any ideas.

Help us make this tool the go-to DAX formatter around the web.

Thanks
Sam

@sam.mckay,

Here’s one I formatted today with DAX Clean Up. #1 and #2 seem like rules violations, and I think the rule for #3 should put the &&/|| on the previous line:

  • Brian

Yep agree with all of these.

Thanks Brian

Few things I noticed/wish list

  • Looks like it doesnt know how to handle { }

  • Feels like the variable name itself should be a different color. Light blue in PBI

  • Doesnt seem to recongize IN as function

  • For me Green usually means comments, not text. Text is usually red in PBI

  • Yellow for the functions can be hard on the eyes. Blue in PBI

  • Ability to change the size of the font (and maybe even chose the font) would be nice. Especially for longer functions

  • The “Edit, New, Copy Code” buttons at the bottom, maybe put those at the top. With longer functions, dont really know those buttons are down there

  • Clear current function

  • Export button to Text File, Word Doc, maybe even a DAX Studio file, and Visual Studio Code

  • Toggle on/off setttings

  • Light Mode and Dark Mode Options

  • Other measures used should be a different color, purple in PBI

3 Likes

Thanks Nick, great points

I agree with Nick … anything that can be done to make the user feel more at home is great (e.g., same colours as PBI Desktop [red strings, green comments, blue DAX keywords, {mauve?} measures], etc.). Perhaps an “undo” button as well, in case someone wants to easily start again from the same unformatted DAX.

Greg

Ok thanks Greg, good thoughts

As a follow up on all these.

We’re working on them.

Have some quite interesting ideas also around the entire Analyst Hub concept.

Will share more details soon.

Sam

Indenting is a bit off

It seems fine to me. What is incorrect there in your view?

The 4 variables should be indented just like SUMMARIZE and then further indented inside CONCATENATEX, example:

Selected Months = 
VAR SelectedMonths =
    VALUES ( Dates[Month Number] )
VAR MonthNumbersAndNames =
    SUMMARIZE ( Dates, Dates[Month], Dates[Month Number] )
VAR Result =
    CONCATENATEX (
        SUMMARIZE ( Dates, Dates[Month], Dates[Month Number] ),
        VAR CurrentMonthNumber = Dates[Month Number]
        VAR CurrentMonthName = Dates[Month]
        VAR IsPreviousSelected = CurrentMonthNumber - 1 IN SelectedMonths
        VAR IsNextSelected = CurrentMonthNumber + 1 IN SelectedMonths
        RETURN
            IF (
                NOT ( IsNextSelected && IsPreviousSelected ),
                CurrentMonthName & IF ( IsNextSelected, "-", ", " )
            ),
        "",
        Dates[Month Number]
    )
RETURN
    LEFT ( Result, LEN ( Result ) - 2 )

Hmmm ok yes I see the issue now.

That is an interesting one.

We’ll have to work on this over time.

Thanks for the idea

Sam

1 Like

HI,

Measure =
SUM ( Table[Sales] )
  1. Should have indentation .
  2. Table is a key word. If you use this PowerBI it will give you an error. So probably put a ’ ’ so it convert to 'Table ’ .

Regards,
Harsh Nathani