Format all DAX in your model at once with Tabular Editor
USE TABULAR EDITOR TO FORMAT ALL DAX IN YOUR MODEL
…turn them into macros to format all your DAX with the push of a button. Easy!
CLICK TO DOWNLOAD C# SCRIPTS FOR TABULAR EDITOR
C# Scripts in Tabular Editor
A handy feature of Tabular Editor is the ability to create C# scripts to simplify & automate basic development tasks. This is a feature available in both Tabular Editor 2 (TE2; free version) and Tabular Editor 3 (TE3; licensed version), though in TE3 these scripts can be turned into macros for easier repeated use in the GUI.
One such task that can be easily automated is the formatting of DAX code in the data model. Particularly upon inheriting a data model from someone else, formatting the DAX is often the first thing we tend to do. This is already super easy in TE and can be done with the push of a button. But if you have a lot of measures, calculation groups, calculated columns & tables… it gets tedious.
C# Scripts in TE (shown: TE3)
For measures, there is a method described for doing this in the TE docs, and some other good blog posts like this one from Ed Hansberry which sent me into the rabbit hole of trying out C# scripting in TE. Since then it’s been a nice thing to experiment with to streamline my Power BI workflow. I had never used C# before so it was interesting to use it as a new avenue for learning.
From Calculation Groups to Columns: Format all the things!
I needed to be able to format DAX for a lot of calculation groups, as well as other objects. So I took what I learned from others and made the below scripts to format & count the objects, returning a pop-up upon execution of the script / macro. The below scripts work for:
Hope they are useful. I still might see if I can make one for Roles & Detail Row Expressions.
*As described in the TE docs, TE has limitations in altering the model in Power BI Desktop. It is thus not possible with a connection to Power BI Desktop to format DAX for Calc. Tables & Columns unless you enable “unsupported features”, which is not recommended. This works fine if you are connected via XMLA endpoint.
How to use the scripts
Note: While I wrote & use these scripts in Tabular Editor 3, they should also work in Tabular Editor 2.
Executes the code.
Open Tabular Editor
Create a new C# script (file > new… > C# script)
Copy & paste the code below
Select “Run Script” (Keyboard Shortcut F3)
(TE3) Select “Save as Macro…” if you want to make it easily re-usable.
Format all Measures (Long-Line format)
var _measures = Model.AllMeasures;
_measures.FormatDax();
// Count & report in an info box the # measures formatted
Info( "Formatted DAX for " + Convert.ToString(_measures.Count()) + " measures." );
Before formatting
Info pop-up
After formatting
However if you look in Power BI, you see that the measure now starts next to the definition. Depending on the length of the first line of DAX code, this can be distracting, so it’s sometimes preferable to include a new line at the start when formatting. This is also described in Ed’s blog post.
The first line is together with the measure name after formatting with the simple syntax.
Format all Measures (Long-Line format with leading new line)
var _measures = Model.AllMeasures;
foreach ( var m in _measures )
{
m.Expression = "\n" + FormatDax(m.Expression);
}
// Count & report in an info box the # measures formatted
Info( "Formatted DAX for " + Convert.ToString(_measures.Count()) + " measures." );
Another advantage of this syntax is you can easily adjust it to format all measures with short-line instead of long-line format. To format in short-line you just have to add one argument - shortFormat: true
Format all Measures (Short-Line format with leading new line)
var _measures = Model.AllMeasures;
foreach ( var m in _measures )
{
m.FormatDax();
m.Expression = "\n" + m.Expression;
}
// Count & report in an info box the # measures formatted
Info( "Formatted DAX for " + Convert.ToString(_measures.Count()) + " measures." );
Format all Calculation Group items at once (Short-Line with leading new line)
The below code will format & count calculation items in the model. This can be done in both Power BI Desktop & cloud-published data models, so I like to have this done together with the measure formatting, usually, in a single macro.
int _counter = 0;
foreach ( var _calcgroup in Model.CalculationGroups )
{
foreach ( var _item in _calcgroup.CalculationItems )
{
_counter = _counter + 1;
_item.FormatDax();
_item.Expression = "\n"_item.Expression;
}
}
// Count & report in an info box the # Calculation Items formatted
Info( "Formatted DAX for " + Convert.ToString( _counter ) + " calculation items." );
Format all Calculated Tables at once (Long-Line format with leading new line)
Note: Will not work on Power BI Desktop unless “unsupported features” are enabled, which is not recommended.
foreach ( var _tables in Model.Tables )
{
if ( Convert.ToString( _tables.Columns[0].Type ) == "CalculatedTableColumn")
{
_tables.Partitions[0].Expression = "\n" + FormatDax( _tables.Partitions[0].Expression, shortFormat: true );
}
}
// Count & report in an info box the # Calculated Tables formatted
int _nrtables = Model.Tables.Where(_tables => Convert.ToString(_tables.Columns[0].Type) == "CalculatedTableColumn").Count();
Info( "Formatted DAX for " + Convert.ToString( _nrtables ) + " calculated tables." );
Format all Calculated Columns at once (Long-Line format with leading new line)
Note: Will not work on Power BI Desktop unless “unsupported features” are enabled, which is not recommended.
foreach ( var _columns in Model.AllColumns )
{
if ( Convert.ToString( _columns.Type ) == "Calculated")
{
(_columns as CalculatedColumn).Expression = "\n" + FormatDax( (_columns as CalculatedColumn).Expression, shortFormat: true );
}
}
// Count & report in an info box the # Calculated Columns formatted
int _nrcolumns = Model.AllColumns.Where(_columns => Convert.ToString(_columns.Type) == "Calculated").Count();
Info( "Formatted DAX for " + Convert.ToString( _nrcolumns ) + " calculated columns." );
Thanks to Mateusz Mossakowski for informing me about the deprecation of FormatDax().
FAT! - Format All Things!
The below script (which you can download by clicking on the button) is a combination of all the above.
Keep in mind again that formatting Calculated Tables & Calculated Columns will not work with Power BI desktop unless you enable “unsupported features”, which is not recommended. It does work perfectly fine when connecting to a model using the XMLA endpoint.
Get FAT - format all things!
