Edit/Replace Code Inside All DAX Measures Using Tabular Editor

OK, by now you will likely be getting to understand that I think Tabular Editor is pretty cool. I like every bit about it, and I am learning more every day. Daniel Otykier has done a great job with this wonderful bit of software, and the more I use it the more I love it. One thing that Tabular Editor doesn’t have, however, is a lot of documentation. There is definitely some in the Wiki and some more in the Microsoft documentation, but mortal non-programmers like me are simply going to have to work the rest out ourselves, for now at least. But as the saying goes, “necessity is the mother of invention”. Without a problem there is little innovation, so I love it when I find a problem that needs to be cracked.

Then a Problem Presented Itself

I was working with a client today. He has a complex Power BI DAX model with literally hundreds of measures. Long story, short, we decided that he was going to bulk edit a lot of measures to change the way they were written. The same change in every measure that has the same ‘issue’. I want to recreate a similar problem (not the same problem) using Adventure Works and share with you the solution I came up with to solve such a problem using Tabular Editor.

An Analogous Problem Defined

Imagine you have the following measures in the Adventure Works data model.

Total Chain Sales = CALCULATE(SUM(Sales[ExtendedAmount]),Products[SubCategory]="Chains") 
Total Brake Sales = CALCULATE(SUM(Sales[ExtendedAmount]),Products[SubCategory]="Brakes") 
Total Cap Sales = CALCULATE(SUM(Sales[ExtendedAmount]),Products[SubCategory]="Caps")

There are 37 sub categories in Adventure Works, so let’s assume you have 37 measures that all look like this.

So now imagine that you want to improve the readability of your measures. So instead of this

CALCULATE(SUM(Sales[ExtendedAmount]),Products[SubCategory]="Chains")

You want to have this

CALCULATE([Total Sales],Products[SubCategory]="Chains")

Where

Total Sales = SUM(Sales[ExtendedAmount])

You now have 37 measures to edit if you want to proceed to make the change.

Note, it doesn’t matter if you think it is a good idea or a bad idea to have these measures, nor if you think it is a good idea or a bad idea to make the changes. All I am doing here is creating a use case to demonstrate a problem that can be fixed with scripting in Tabular Editor. If you have a similar problem, then this is for you.

Tabular Editor Script

I was pretty sure Tabular Editor could do this, but without any language reference, I really only had a hunch to go on. As before, I looked on the Wiki and found a line of code as follows:

string.Join

I thought that was interesting, and wondered if there was such a thing as string.Replace. I tested it using the Output() debugger and to my shock, it worked. So here is the script I ended up writing.

/*Warning!  Take a backup copy first.  
This script will operate over every measure in the model. It is 
essential that your FromString and ToString are set to change only the
specific usage of the string that you need to change across the entire model.*/

var FromString = "CALCULATE(SUM(Sales[ExtendedAmount])";
var ToString = "CALCULATE([Total Sales]";

foreach (var m in Model.AllMeasures)
    {
        m.Expression = m.Expression.Replace(FromString,ToString);
        /* Cycle over all measures in model and replaces the 
           FromString with the ToString */
    }

Note that Replace(from,to) is case sensitive, so you need to get the capitalisation right, and possibly repeat for different capitalisation inside formulas.

Change Text in All Measure Names

Daniil from XXLBI.com suggested another use case for me.  Let’s say you have 50 measures all with names like:

• Total Sales
• Total Sales LY
• Total Sales YTD
• etc

Now let’s assume that someone decided it would be better to use the term “revenue” than “sales”. With a small modification, my script can solve that problem too.  Instead of doing the text replace in the measure expression, you would instead do the replace in the measure name, as shown below.

var FromString = "Total Sales";
var ToString = "Total Revenue";

foreach (var m in Model.AllMeasures)
    {
        m.Name = m.Name.Replace(FromString,ToString);
        /* Cycle over all measures in model and replaces the 
           FromString with the ToString in the measure names*/
    }

I tested this script and I am pleased to report that it correct made changes to all dependent measures as well.  So any measure that referenced  [Total Sales] inside the measure formula was also updated and re-pointed to [Total Revenue].

Can You Think of Any Problems This Script Can Solve?

I would love to hear from you if you can think of a problem that could be solved with this script. My hunch is there are some out there, but I would love to hear from the trenches if this can solve any real world problems. Post your comments below.

All My Scripts will be on the Wiki

I have decided to share all of these snippets of code on the wiki along with the others created by Daniel, so you will find them at the bottom of the list here.