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:

Unformatted formula in DAX
Unformatted formula in DAX

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:

Start Tabular Editor from Power BI
Start Tabular Editor from Power BI

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.

Automatically format all measures with Tabular Editor
Automatically format all measures with Tabular Editor

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:

Formatted DAX Measure
Formatted DAX Measure

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:

Remove default aggregation to selected columns
Remove default aggregation to selected columns

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.

Denis Selimovic

Denis Selimovic

As a user from the very beginning, Denis Selimovic is passionate about Power BI and everything related to it. In his blog WhatTheFact.bi, he writes about the latest developments in Power BI and provides tips and tricks on the subject. Besides being a Power BI enthusiast, blog author, speaker and senior consultant, he is also co-organizer of the Meetup Power BI User Group Switzerland.

Kommentare:

5 1 vote
Article Rating
Subscribe
Notify of
guest
5 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Mehdi HAMMADI
4 months ago

Thank you for sharing these tips especially the one to change the default summarization

Claire Allwood
Claire Allwood
19 days ago

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);
}

Claire Allwood
Claire Allwood
15 days ago

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

5
0
Would love your thoughts, please comment.x
()
x
%d bloggers like this: