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!

[Anonymous]

Hi,

First of all nice hack, but i am having trouble to make it work on my side.

Problem is that i am not able to save changes.

When i try to deploy it to pbix file, i am getting error messages.

I am not sure can we save file from Tabluar Editor if file is already openeg from PBI (and file needs to be opened from PBI so we can connect to file at the first place)?

Can you share some advices how did you solved this issue?

[karaoan]

@Anonymous have you followed exactly the steps that I have outlined? In any event: without having exact error description I am unfortunatey in no position to assist. To connect from Tabular Editor the PBIX file needs to be open.

[wckai]

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!

[Anonymous]

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

[Anonymous]

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

[Anonymous]

HI @Anonymous , 

thanks for reply, I tried that, but I couldn't make it work. I will try again, stricktly following your advices.

Thanks again 🙂

[DavidKett]

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!

[alex_itkin]

please review https://www.microsoft.com/store/apps/9N60JMZCMCHC

[Anonymous]

Anyone using this trick with success? 

I just tried with 2.9.1 version of Tabular editor and no luck.

I tried trick from @Anonymous . But even with simplest model where i just want to copy one measure from dataset 1 to dataset 2 it doesnt work.

[Anonymous]

I just found out that secret key is to refresh you data set.

Actually you can just start refresh and then cancel it after few seconds, since PBI desktop is getting all fileds "refreshed" on refresh data action.

Marco Russo has great article on this subject that can be found here. Hope it will help like it helped me.

https://www.sqlbi.com/blog/marco/2017/10/10/offline-model-editor-experience-for-power-bi/

[Anonymous]

##(1) Open the PBIX file. If copying from PBIX Source to PBIX Target have both the source and target files open.

##(2) Open tabular editor and connect to the PBIX model.

** IMPORTANT: Saving the PBIX changes will require the preference setting to be allow PBI features.  Close and reopen Tabular editor so changes will stick.

##(3.1) With source file Regular Measures selected run this script & then Copy (CTRL+C).  This will grab the measures.

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

##(3.2) With Quick Measures selected run this script & then Copy (CTRL+C)

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();}

##(4) Paste the selected measures to Text file for review. 

##(5) Paste the selected measures in Tabular Editor TARGET PBIX file on the table.  You should see the measures added to the table.

##(6) The TARGET PBIX file will not immediately show the new measures. 

** IMPORTANT: In the target PBIX file refresh the data and the new measures should appear!  Save the file!

---

(Step 3) Example of measure syntax copied:

{
  "InstanceID": "976f1b5c-8a57-4872-8c1f-06f2d80ee8ec",
  "measures": [
    {
  "name": "TEMPLATE_1",
  "expression": "\n SUM(myTable[MyFielName])",
  "formatString": "\\$#,0.00;(\\$#,0.00);\\$#,0.00",
  "dataType": "double",
  "modifiedTime": "2020-02-19T15:00:50.623333",
  "structureModifiedTime": "2020-02-19T14:59:26.343333"
},
    {
  "name": "TEMPLATE_2",
  "expression": "\n CALCULATE(SUM(myTable2[MyFielName]),myTable2[Name]=\"NAME EXAMPLE\")",
  "formatString": "#,0.0",
  "dataType": "double",
  "modifiedTime": "2020-02-19T14:53:41.056667",
  "structureModifiedTime": "2020-02-19T14:52:52.806667"
}
  ]
}

[bo_afk]

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?

[alann]

I had the same issue with the 'Paste' option greyed out and unable to Copy/Paste even within the same data model.

What worked for me was going to an earlier version of the 'Tabular Editor' application. The screenshots above showed 2.7.3. I downloaded the earliest verison available today (2.7.4) and upon runnin this version i am able to copy/paste as this post describes.

Thank you for this hack, it has saved me literally hours, if not days updating between documents.  

[VRMar2001]

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

[gbc_batman]

Very good job guys! It worked like a charm.