Improvements needed to the DAX editor in SSAS Tabular
Many developers abandon SSAS Tabular (after their companies pay for enterprise licensing to get it) because of the development experience and they reason that if the development experience is poor that the product may be just as unreliable. The model designer has many problems and quirky behaviors when the model becomes complex (typically due to the number of tables & measures). In particular, the DAX editor can be very unwieldy. The auto-completion feature can cause more mistakes than it prevents. For example, if you were to backspace over a table and column, and then use the object list to select a different object; the editor makes a horrible mess by combing the old and new text. Some examples:
In trying to replace 'First Table'[First Column] with 'Second Table'[Second Column] the editor may produce:
'First Table'[First Co[Second Column]lumn] or ''Second Table'[Second Column]. Given the small font and text size in the editor, users miss the doubled-up single quotes.
When an error is reported, changes are lost; developers become angry & moody and are mean to people around them.
After making changes to an expression and pressing Enter to save changes, the editor shows the previous expression, causing the developer to think that changes were lost which makes them throw things and use bad words.
While you're at it, add support for comments and tabs.
I suggest a usability test be conducted and observed using a production-scale project.
"When an error is reported, changes are lost; developers become angry & moody and are mean to people around them."
and this.. "the editor makes a horrible mess by combing the old and new text."
But mostly THIS: "When an error is reported, changes are lost.."
Grrrrrrrrrrrrrr! Graaawwwww Grumble Curse!! Curse!! MFucr!! WTF!
Marc Solomon commented
I have to agree with previous comments, I model in Power BI Desktop then migrate my code to Tabular model. It is a little painful and makes development time consuming.
Sam Carroll commented
Not to mention it clears out your formula sometimes if you enter an invalid complicated formula! Its not even equivalent to notepad IMO.
Mike Honey commented
Count me as one who has used "bad words" at this - I think calling it an "Editor" is almost ridiculous - it's a poorly conceived and executed "Text Box" at best. Perhaps this was OK when v1 came out, but that was over 5 years ago!
Note there are a couple of open-source efforts that is which should be considered IMO:
To extend this a bit, I've seen presentations where people can develop DAX expressions in Excel and then create SSAS cubes from those with minimal modification.
The flip side of that situation, as experienced by my organization, is we have access to vendor-built SSAS cubes for a major application but we can't determine whether to buy the hardware necessary to install the cubes without first knowing how well they might suit our needs.
Therefore, having the ability to extract asdatabase definitions for testing in Excel DAX or making it possible to deploy a prototype to either Excel Power Query or SSAS would make it easier to quickly test code logic before configuring the entire ETL process for production cubes.
Jakub Kaczanowski commented
agree. The excelesque formula bar just doesn't work...