We need to admit, the GUI for Power BI Formula Bar (DAX) and the Power Query Advanced Editor (M) are not the most user-friendly.
DAX editor (formula bar) is getting better but still quite limited. M editor (Advanced Editor) … OK, this might be something more advanced and eventually not everyone will need to write M functions.
But just in case if you are interested on it, these are my Helpers to write and format DAX and M code:
-
- DAX STUDIO – “DAX Studio is a tool to write, execute, and analyze DAX queries in Power BI Designer, Power Pivot for Excel, and Analysis Services Tabular.” by SQLBI
- DAX FORMATTER -“DAX Formatter is a free tool that transform your raw DAX formulas into clean, beautiful and readable code.” by SQLBI
- POWER BI HELPER – “Export the Entire M Power Query Script from a Power BI File” by RADCAD
- NOTEPAD++ – “Notepad++ is a text editor and source code editor for use with Microsoft Windows.” by Don Ho
Connect the Power BI file (or excel) to DAX Studio and you have now a powerful tool to:
- – Analyse the model metadata;
- – Evaluate queries and get the results on a grid or exported to a file
- – All DAX functions listed, grouped by type and explained. Really useful when you need to check different scenarios and validate the results.
- – Write the code in a clean and readable manner and debug.
DAX Formatter is a useful tool if you what to format your code on the fly.
A code well formatted and commented makes a huge difference. Think every time you had to take over a PBI done by others and the amount of time you spent trying deciphering what that function was. And how many times do we have to go back to a work we did long time ago and now we don’t remember anymore what we have done?
So DAX Formatter is a great help to format DAX functions to be easily read by everyone.
Just copy and past your DAX Function into the box…
…click on FORMAT…
…now it is much more clear.
Power BI Helper is the solution to extract all the M script from a model. You can read it directly in the tool if you are looking for a simple piece of the puzzle or export it to a file where can be editable using for example Notepad++ or Visual Studio.
One big advantage is that you can reuse, share or edit your M Code much more easily than in the Advanced Editor in the Power Query Editor.
To use Notepad++ one needs first to define DAX and M as a language in Notepad++ as these are not among the default languages included in the package.
You can create new User Defined Language manually from the scratch or alternatively import it from an html file.
You can download my language settings file from GitHub: DAX HERE and M HERE .
These are my preferences but you can edit these to suit yours.
Save the files directly in the APIs folder as shown below
and enable the Auto-completion and Auto-Insert in Preferences menu to get Intellisense when you write your code
In the end you should have something like it
To keep the list of DAX functions up-to-date, check out the MSDN page here and add any new functions to the language file.
UPDATE 13/05/2018
A big thank you to sascha-kasper.com for the fantastic work on add Parameters, Return Value and Descriptions to Autocomplete. It works pretty well 🙂
Hi Jose,
based on your feedback, I updated my version of the XML file for Notepad++ (now version 3.01) to include:
– Syntax Highlighting
– Auto-complete
– Parameters
– Return Value
– Description
See the updated post for details: http://www.sascha-kasper.com/dax-syntax-highlighting-for-notepad/
I did only preliminary testing, so if you find any issues, please let me know.
Hi Sascha,
Fantastic work. Kudos for you. It works perfect for me and I am pretty sure it is going to facilitate the life of many of us.
Thanks for sharing.
thank you. much appreciated. glad it works!
Quick note:
Added newly created DAX functions per November 2018.
ISINSCOPE
COMBINEVALUES
UTCNOW
UTCTODAY
NORM.DIST
NORM.INV
NORM.S.DIST
NORM.S.INV
T.DIST
T.DIST.2T
T.DIST.RT
T.INV
T.INV.2T
Thanks for sharing @skasper. I’ve updated my files with those.