Bulk measure handling (copy between reports/format changes)

[karaoan]

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!