This post is also available in:
Deutsch
One topic that I think doesn’t get enough attention is the ability to automate work in Power BI with the Tabular Editor.
Especially the official documentation gives some nice insights into what the scripting engine can achieve.
I would like to present a few examples in this article.
Example 1: Automatic formatting of all measures
If you often work with Power BI, you know the situation when you open a report and all measures look like in the following picture:

Without proper formatting, it’s hard to tell here what the formula is doing at all. If all measures in the report look like this, there is a big frustration. However, the Tabular Editor’s scripting engine provides a remedy for this. This can be started easily via the external tools:

In the Tabular Editor, the following C# code can now be entered via the “Advanced Scripting” field:
Model.AllMeasures.FormatDax();
After a click on “Run Script” (green arrow) or a click on the F5 key the code will be executed.

After that the changes have to be written back from the Tabular Editor into the opened Power BI file (Ctrl + S) or File –> Save and then all measures in the Power BI file have already been formatted:

Example 2: Replacing names and formulas
Let’s assume the case that a calculation is often performed in a report, for example, the calculation of sales figures:
Sales US = Calculate(SUM(myTable[SalesAmount]), Country="United States")
The report now used SUM(myTable[SalesAmount] several times as part of the calculation. For maintenance reasons, however, it is more clever to replace the SUM formula with a Measure. This can also be done by script with a foreach loop:
// Replacement within a Measure
var SearchString = "SUM(myTable[SalesAmount])"; // set the search string
var ReplaceString = "[Sales Amount]"; // set the replace string
foreach (var m in Model.AllMeasures)
{
m.Expression = m.Expression.Replace(SearchString, ReplaceString);
}
Occasionally, names also change within a report. For example, if instead of “Sales Amount” only “Sales” should be used. Here, too, the Tabular Editor offers the possibility to go through all elements or measures with the foreach loop and to exchange texts accordingly.
The corresponding script would be:
// Replace a string in a Measure Name
var SearchString = "Sales Amount"; // set the search string that you want to replace
var ReplaceString = "Sales"; // set the replace string
foreach (var m in Model.AllMeasures)
{
m.Name = m.Name.Replace(SearchString, ReplaceString);
}
One thing to note about the latter, however, would be that the visuals would still use the old name of the measure.
Example 3: Change Default Summarization
Another example would be to set the default summary of all or individual columns to “Do not summarize”.
Here again there would be the possibility to adjust this for all columns:
foreach(var c in Model.AllColumns)
{
c.SummarizeBy = AggregateFunction.None;
}
Probably needed more often in reality would be the possibility to select multiple columns and then set the selected columns “Do not summarize”.
foreach(var c in Selected.Columns)
{
c.SummarizeBy = AggregateFunction.None;
}
To do this, simply select the desired columns and run the above script:

Save actions as “Custom Action”
One last little tip. The scripts just mentioned can be saved as Custom Actions in the “Advanced Scripting” window via the plus sign. This way, you can instantly fetch the scripts you often need with a mouse click.
Thank you for sharing these tips especially the one to change the default summarization
Thank you Mehdi, I really appreciate the feedback 🙂
Yes, it has really huge potential. And once you start, you will see more and more possibilities!
Hi, I was so excited when I found Tabular Editor. I come from a VBA background, if I did anything more than 3 times, I automated it. So this is right up my alley.
Unfortunately, the very simple C# script replacing a string in all measures just isn’t working for me. I changed a column name, and want to change all the measures that are now broken. I have copied the script faithfully and checked and rechecked. I have ticked the “Allow unsupported Power BI Features’ preference. I still get ‘Script executed successfully, 0 model changes’. Any ideas?
// Replacement within a Measure
var SearchString = “2021 EQUIV DATE”; // set the search string
var ReplaceString = “EQUIV 2021 DC SQL”; // set the replace string
foreach (var m in Model.AllMeasures)
{
m.Expression = m.Expression.Replace(SearchString, ReplaceString);
}
Hi Claire,
in general, your code looks good to me. Did you copy the name of the column from the measure?
If your file doesn’t contain sensitive data, you can also send me a message with the details in the about me section.
Best regards
Denis
Thanks so much for answering, Denis, just saw this…Turned out what I thought I had done as measures were columns. So of course this wouldn’t work (der). I fear that Power BI does not allow the same kind of manipulation on columns. Is that true? if not, do you know the C#? I need them to be columns cause I use them in a hierarchical slicer. 🙂 Claire