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.

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

1. Hello Matt,

this is awesome… is there any way to change the M code for a table partition? I mean updated it…

1. I’m not sure what you mean by “table partition”

2. Is there something I need to put in the script to point to all measures
what is Model. AllMeasures I have all my measure in Tables then __MT

I want to change the name of table reference in all measures from zCategory to xxxCategory
var FromString = “History[zCategory])”;
var ToString = “History[xxxCategory])”;
foreach (var m in Model.AllMeasures)
{
m.Expression = m.Expression.Replace(FromString,ToString);
}

3. Hi Matt, I’m very new with Tabular Editor and i think i can use your script a lot!
one thing i didn’t find in the article, is where to add the script and how to run it (the basics). Appreciate if you can assist with short explanation. Many Thanks!!

4. Hi,
Can i Edit/Replace Code Inside All DAX calculated tables ?
If yes, can you give me a script ?
CM

5. Hi Matt

thank you for a great post, do you have an idea about how you might be able to insert something after, instead of replacing

1. I’m not sure what you are asking. You can manually insert anywhere you like. If you need to do an auto insert in many locations, you need to have a way to identify “where” to insert. Typically I would do

Search “Text in doc”

Replace with
“Text in doc[Extra text]”

6. Hi Matt,

I am a novice but it looks like this will change all measures. Is it possible the make it only change measures in a selected folder? I’ve created my measures for sales but need to replicate them for quantity.

Thank you.

1. in the foreach line you can use this:

foreach (var m in Selected.Measures).

Use CTRL to multiselect the measures on the left navigation pane

1. Here is the full script.
Make sure to select the measures before running:

var FromString = “FindSomeText”;
var ToString = “ReplaceWithThisText”;

foreach (var m in Selected.Measures)
{
m.Expression = m.Expression.Replace(FromString,ToString);

/* Cycle over selected measures in model and replaces the
FromString with the ToString */

}

7. This is brilliant!!!!!!!

I have inherited a Power Bi model that needs extensive changes. The main issue is that there are two date tables used interchangeably (no role playin dimensions) and the measures do not update properly when changing the table names. In desperation I googled and found this script. It has helped me immeasurably by enabling me to change the names of the old tables to the correct tables with out having to to it manually.

THANK YOU!!!!!!!!!!!

1. I’m glad it helped. When I first realised it was possible, I tried to post the script with the other “useful scripts” on the TE website, but Daniel rejected it and didn’t think it had much application. It seems like a good and useful solution to me, so I’m glad it helped someone. You should also check out TE 3. There is a new scripting feature that allows you to extract all measures into a text file. From there you could use MS Word to do bull editing prior to running the final script in TE again to change all measures. I cover it here https://exceleratorbi.com.au/5-productivity-benefits-of-tabular-editor-3/

8. The first few times it used this it worked great! Now when I use the code it says everything runs successful, but there are zero model changes. Did something change with Tabular Editor or with Power BI Desktop?

1. Definitely yes to Power BI Desktop – it changes every month. You should update Tabular Editor and try again.

1. I did update to the most recent version and still the same. Although I am not on the paid version of Tabula Editor. I installed 2.16.1 frim GitHub. Thoughts?

1. I don’t know. You will have to log a bug/question on the official website.

1. I figured out the issue. It was user error. Can’t believe it took me this long either. I was trying to use the name script when I needed to be using the expression script. Boy do I feel embarrassed and like a total newbie. At least I figured out the issue and am back to using your excellent tools!

9. Why is this unavailable to edit columns with Tabular Editor&

1. It is not supported by Power BI Desktop. There is a setting that you can use to turn on unsupported features, but you do so at your own risk.

10. Hi Matt,

Great trick ! I wonder why this is not implemented in PBI directly… however, I am struggling to replace a text containing a quotation mark. Namely, I would like to replace this
A_TimeIntelligence[Name]=”YTD”
by this
A_TimeIntelligence[Name]=”Cumul ex.”
Problem is, “A_TimeIntelligence[Name]=” + “”” + “YTD” +””” is not accepted…

Thank you

Christophe

11. Is there a way to write a script in tabular editor to convert the selected implicit measure column to Proper Case.
Example : Need to create a SUM measure for COST_QTY and rename it to “Cost Qty”? I had a look at the code available here – https://docs.tabulareditor…. but it seems the C# function to use .ToTitleCase is not supported. Any comments will be of great help.

1. Hi Naresh,
You can certainly use .ToTitleCase in a Tabular Editor script, but since this method belongs to the TextInfo class, you have to obtain a reference to a TextInfo object first:

using System.Globalization;
var textInfo = CultureInfo.CurrentCulture.TextInfo;
textInfo.ToTitleCase(“cost_qty”).Output();

You would probably still need to get rid of any underscores using string.Replace(…) and maybe you’d also need to convert the entire string to lowercase using string.ToLower() before calling .ToTitleCase(…). Hope this helps!

12. Mate this is perfect. Worth several beers. Thank you.

13. Great article, thanks Matt.

14. charlie taylor

I’m trying to create a data dictionary for my datasets. I think a potential workflow is writing into the field descriptions then exporting in bulk. From there, I’d put into a spreadsheet unless I can find a better, more user friendly solution.

Is Tabular Editor a good way to edit descriptions, and are there any scripts you know of that might help?

Any data dictionary tips also appreciated!

1. DAX Studio has functionality that helps with that.
Steps:
1) Install as extension to Power BI.
2) External Tools -> DAX Studio.
3) Look for DMV tab in bottom left corner.
4) Find the query that you want to run and double click.

There are several general categories of functionality (TMSCHEMA, DBSCHEMA, DISCOVER) so it may take some time to determine which ones you want to use.

15. 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*/
}

2. Yes, in line 9, replace Model.AllMeasures with Selected.Measures

16. 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 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.

17. 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.

18. I currently do this via Excel/VBA
I have a Bulk Load / Delete Sheet in my Excel File
With a Table containing Columns
MeasureName,
Expression,
Format(Drop Down containing, Decimal, Percent, General),
Exists (Autopopulated)

Here is a screenshot

https://1drv.ms/u/s!AiKBTsYfZw-vgv56y5oSOVhGkM0Wyg?e=qROv3C

Very useful in doing bluk upload of measures to the Data Model

Cheers
Sam

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.

1. @Matt – No, This is a Excel only solution for Bulk Upload or Bulk Delete of Measures to the Data Model in an Excel File

19. 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);

Scroll to Top