cancel
Showing results for 
Search instead for 
Did you mean: 
karaoan

Bulk measure handling (copy between reports/format changes)

A requirement that we face very often in our projects is to be able to easily re-use multiple measures particularly when dealing with similar data models on a standard data source like for example cloud-based accounting systems like Dynamics, Xero, QuickBooks etc.

 

One option are standardized .pbit templates that are automatically generated in Acterys  but these can’t be used in every situation. Power BI so far doesn’t offer a script view like SSAS MD where you can easily copy measure definitions from one model to another. You also can’t select multiple measures and copy them from one report to another. Using just Power BI you have to copy one measure each for every measure you want to reuse.

A bit annoying… But there is a great way out. Not officially supported by Microsoft but it works.

The answer is “Tabular Editor” a godsend for us consulting professionals that have to reuse measures and apply bulk actions to measures on an ongoing basis.

 

A big Thank You! here goes to Daniel Otykier who is the mastermind behind the Tabular Editor, and who helped me a lot refining the technique that I am covering here. Daniel should, IMHO, be a very hot contender for developing the “Most Helpful Power BI Community Tool” (on a similar level as DAX studio – shout out to Darren, Marco and Rui).

The Tabular Editor offers great editing and scripting features for SSAS Tabular models the basis for Power BI.

How does it work?

Just connect Tabular Editor to Power BI:

Danger, Danger Will Robinson

You will get a warning that what you are about to do is dangerous and not officially supported:

No risk no fun…

But I highly recommend doing as the dialog tells you and having a backup of your file.

After you have made the big leap to the dark “unsupported side” and have connected your model you can navigate your model, tables and all measures for example:

From here you can now select as many measures as you like, copy them into the clipboard or apply other bulk actions.

BUT halt no so quickly….

There is one important thing you have to do beforehand: The copy process doesn’t like format annotations with the measures. So, if you proceed just this way you will get an error once you are trying to save the pasted measures in your destination model.

When I encountered this problem, Daniel came to the rescue and suggested to apply a script that removes the format annotations from the measures. It’s one simple line:

 

Selected.Measures.ForEach(m => m.RemoveAnnotation("Format"));

 

The Trick

To make it work -just select all your measures that you want to copy and copy this script into the “Advanced Scripting” Tab and run the script (click on green triangle):

That’s it. Now you can just press CTRL-C or go to "Edit" -> "Copy" and your measure definitions are in the clipboard.

The only step left is to open your destination power BI .pbix file with another instance of the Tabular Editor. Navigate to the relevant table and paste from the clipboard. Voila all your measures are there.

 

A similar approach can also be used for other bulk operations. For example, if you want to apply the same number format to multiple measures at once, just select them and choose the format string in the Tabular Editor properties.

One last thing: In case you are using Quick Measures: these are also not liked that much by the copy and paste operation. For it to work you have to run this script that Daniel developed. It will remove both the format annotation and the extended properties.:

 

var extProp = typeof(Measure).GetProperties(    System.Reflection.BindingFlags.Instance |    System.Reflection.BindingFlags.NonPublic     ).FirstOrDefault(p => p.PropertyType.Name == "Measure" && p.Name == "MetadataObject"); foreach(var m in Selected.Measures){    m.RemoveAnnotation("Format");    var tomObject = (Microsoft.AnalysisServices.Tabular.Measure)extProp.GetValue(m);    tomObject.ExtendedProperties.Clear();}

 

For any further questions send us line or chat with us!

Comments

Thanks much for this hack! I was looking for something like that, and this will save a lot of time  - I need to merge pages from separately developed PowerBI files (though the same data source), and I really didn't want to have to individual type in each measure used across the different files. 

 

I, too, was having trouble saving the file in Tabular Editor - there was an error saving that had something to do with the Extended Properties, but that 2nd script to run at the end of this article solved the problem for me (I wasn't even trying to copy over quick measures or anything) and now things seem to work.  Thanks!

HI, I did exactly as  you pointed, but after save no change is visible in open .pbix file? Advice pls, thanks

Hey Tomislav, 

 

I ran into the same issue, but ended up fixing it. I dont know which of these two things fixed it, so try both:

 

1: Make absolutely sure you don't create any duplicate measures. The tabular editor will put a nice (1) next to the duplicates, but it might have been the reason it didnt work for me.

2: This seems silly, but after you change and save the data model in the Tabular editor, make sure you also save your powerBI file separately. The file wont show the model changes immediately, only after you close and reopen the file. However, when you close the file, it doesnt ask you to save the file automatically as usual. I just removed and added a random filter so the file thought there were changes to be saved. Then I saved it, reopened it, and the new measures werte there 🙂

 

Hope this helps!

 

Jaap

Thanks for the trick. Has anyone been able to use this for copying measures from a SSAS Tabular model to a PBI data model? Any help would be greatly appreciated!

Hi, I can't actually paste the measures from one editor to another. After I click ctrl+v (after copying), nothing happens. When I go to edit - > paste, this option is greyed out.

 

It could also be that when I try to copy from the other editor that this command doesn't register. Any ideas why this is the case?

I'm trying to install the tabular editor. I already downloaded the AMO library, but I'm getting this message. Please help. Thank you!

VRMar2001_1-1603152005329.png

 

 

Polls
What is your favorite Power BI Feature release in July 2021?