Automation in Power BI – Format all measures, replace text, set default summarization and more

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.