Skip to main content

Well, by now you probably understand that I think the Tabular Editor is pretty good. I like everything and I learn more every day. Daniel Otykier has done a great job with this wonderful software, and the more I use it, the more I love it. However, one thing that Tabular Editor doesn't have is a lot of documentation. There is definitely something in the Wiki and a few more in the Microsoft documentation, but deadly non-programmers like me will just have to figure the rest out ourselves, at least for now. But as the saying goes, "Necessity is the mother of invention." No problem, there is little innovation, which is why I love it when I find a problem that needs to be solved.

Then a problem arose

Today I was working with a client. You have a complex Power BI DAX model with literally hundreds of measures. In short, we decided that I was going to mass edit many measures to change the way they were written. The same change in all measures that have the same 'problem'. 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 problem using Tabular Editor.

Definition of an analogous problem

Suppose 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 subcategories in Adventure Works, so let's say you have 37 measurements that look like this.

So now imagine that you want to improve the readability of your measurements. 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 measurements to edit if you want to proceed with the change.

Keep in mind that it doesn't matter if you think it's a good idea or a bad idea to have these measurements, or if you think it's a good idea or a bad idea to make the changes. All I'm doing here is creating a use case to demonstrate a scriptable problem in Tabular Editor. If you have a similar problem, this is for you.

Tabular editor script

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

string.Join

I thought it was interesting and wondered if there was such a thing as a rope. Replace. I tested it using the Output () debugger and to my surprise it worked. So here is the script that 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 correctly capitalize and possibly repeat for different uppercase within 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 to date
  • etc

Now suppose someone decided that the term "income" would be better than "sales." With a little modification my script can solve that problem too. Instead of doing text replacement in the measure expression, you should 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 am happy to report that it also fixed changes made to all dependent measures. So any metrics that reference [Total Sales] within the metric formula was also updated and re-pointed to [Total Revenue].

Can you think of any problems this script can solve?

I'd love to hear from you if you can think of a problem that can be solved with this script. My hunch is that there are a few out there, but I'd 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 these code snippets on the wiki along with the others created by Daniel, so you will find them in the at the end of the list here.

error: Attention: Protected content.