Edit/Replace Code Inside All DAX Measures Using Tabular Editor - Excelerator BI

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.

12 thoughts on “Edit/Replace Code Inside All DAX Measures Using Tabular Editor”

  1. Hi again.

    About “Change Text in All Measure Names”. Is there any way to do this for just a few measurements, for example only for selected measures?

    Thank you very much.

    1. Hi, I can’t edit my comment but I think something like this works:

      “Change Text Measure Names in selected measures”

      var FromString = “M_”;
      var ToString = “m”;
      foreach (var m in Selected.Measures)
      {
      m.Name = m.Name.Replace(FromString,ToString);
      /* Cycle over selected Measures in model and replaces the
      FromString with the ToString in the measure names*/
      }

      Thanks for your contributions.

  2. Hi, all.

    The efficiency/flexibility in modeling has caused me the problem that was described by Matt at the beginning of this post.

    Sometimes the environments are very dynamic. There is always something to change: perhaps the scope has changed, perhaps a measure needs to be optimized (or 100 in the same way), perhaps I have learned something new and now I would like to implement it (I am a beginner, I learn something new every day ), perhaps…What would be the most efficient way to do this changes? Without a doubt, tools like Tabular Model can be the solution.

    Last week, I was looking for a way to find/replace text to bulk modify project measurements in Power BI. I tried it with DAX Studio but, I don’t know if due to the lack of knowledge of the tool, I couldn’t find a way to do it. Finally, I spent a few hours renaming, editing and copying measures one by one manually into Power Bi. Many of them only changed in one word (the problem is similar to the problem Matt makes with Adventures Works).

    Yesterday, I quickly tried to run the sripts that Matt show in this post and everything everything seemed to be going well. The only problem, I don’t know if it is something that I don´t do well, is that the visuals I had in Power BI “lost” the reference of the measure they used and I would have to edit and format them again one by one with the new measures names (I think this behaviur it is normal and there is no way at the moment to avoid this rework).

    I haven’t played much with Tabular Editor yet. Since Tabular Editor it is not “supported” by microsoft, my question is the security with which these scripts can be executed without damage to the model. Perhaps my lack of knowledge about this tool is the only reason for this question.

    I intended to be shorter but … Anyway …

    All comments in this post are very useful for me. In my case I come from Excel/VBA and I feel like in this environment. The solution proposed by Sam seems very friendly and powerful.

    Thank you for yor comments.
    Thank you very much for sharing.

    1. Support is coming soon, Luis. I am surprised the measure name changes didn’t flow through. I would expect that to be fixed in a future release. Sorry, I haven’t tested it.

  3. Gilbert Quevauvilliers

    Hi Matt another great blog post.

    I was watching the video on PowerBI.tips where they had Daniel showing the scripting language. And you can use IndexOf when searching for a string instead of Replace.

    IndexOf is Not case sensitive.

    1. Do you mean you upload the Excel sheet using Tabular Editor? I have exactly the same concept in my mind, and will probably extend my Local Host workbook and create a Tabular Editor script to support it.

  4. It would be useful to export all of the measures and their associated table to a single “TSV” file Leveraging the sample code
    // Export properties for all of the measures.

    var tsv = ExportProperties(Selected);
    SaveFile(“Exported Properties 1.tsv”, tsv);

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top