Copy Measures Between 2 Power BI Files - Excelerator BI

Copy Measures Between 2 Power BI Files

I first wrote this article back in Jan-2019 and have decided to come back and update it now.  With the recent enhanced External Tools Toolbar in Power BI Desktop, there has never been a better time to start to learn how to use these tools.

Have you ever wondered if it were possible to move all the measures from one Power BI PBIX file into another?  Imagine you have 2 PBIX files with similar data models (similar enough that the measures will be substantially the same).  It doesn’t matter how you got to this point – the point is if this is you, there is a hell of a task to move the measures over, one by one.  I am not saying this is the best way to create a new similar workbook, I am just saying if you find yourself in a situation where you have measures in one workbook and need them in another workbook, then this is the easiest way to do it.

Tabular Editor Warning!

Ok, here is the warning.  Tabular Editor connects direct to your running versions of Power BI Desktop.  Things can and do go wrong, so you should create a backup copy of any file before you unleash Tabular Editor on that file.   Consider yourself suitably warned 🙂 .

How Do I Do it Already?

First of all you need to open both of your PBIX files in Power BI Desktop. I am using the Aug-2020 version for this demo.  You need to turn on the preview feature for enhanced metadata with this version. Older versions may perform differently.

Preview

You will then have 2 running instances of PBI Desktop running on your PC.  Make sure you are clear which is which.

2 Windows

Launch Tabular Editor (Twice)

Go to each version of PBI Desktop, one at a time, and launch Tabular Editor. Do it carefully, one at a time, making sure you know which version belongs to each PBIX file.

Te

If you don’t see the External Tools or Tabular Editor, then read my article here to find out how to get these.

You should now have 2 instances of Tabular Editor open, one connected to each running instance of PBI Desktop.

Copy the Measures

First you have to make the measures in the source file visible (if you can’t see them).

  • In Tabular Editor, from the view menu (1), select Measures (2).  Note the measures are visible (3 below).

image

  • Multi select all measures from the list (the ones you want to copy, anyway).  Select the first one, hold Shift and click the last one.
  • Copy the measures (Ctrl+C)
  • Switch to the second instance of Tabular editor
  • Select the destination table to store the measures.
  • Paste the measures (Ctrl+V)
  • Click save on Tabular Editor in the receiving workbook.

After saving, you should be able to see all the measures in the destination workbook.

Potential Issues

Mismatch in Table and Column Names

One thing that could happen is that you have slightly different column and or table names in your destination file.  I tested this in 2 ways.

  1. I changed the name of the Sales table to Sale and repeated the copy.
  2. I renamed one of the columns in the Sales table from ExtendedAmount to ‘Extended Amount’

In both cases the measures copied across OK, I just had to edit the measure to correct the table/column name for the new names.  You can do this directly inside Tabular Editor, or you can do it in Power BI Desktop.

As a general tip, I recommend you use Tabular Editor as follows.

  1. Open it when you want to use it.
  2. Use it – exclusively, saving the changes when you are ready.
  3. When you are done, close it down.  You can easily reopen it again later if needed.

Doing it this way makes sure that you don’t get any model conflicts by making changes in both Power BI and Tabular Editor in parallel

Wrap Up

What do you think?  Is this a useful trick?  How much time will it save you?  How did you get into the situation where you needed to copy measures?  Please share your comments below.

28 thoughts on “Copy Measures Between 2 Power BI Files”

  1. It would be interesting to see a script for the Tabular Editor to turn off summarize by for the model. Even better to make it an external tool for PowerBI.

  2. Great technique. Thanks. As a follow up is it possible to copy tables with calculated columns from one file to another ?

  3. Thanks Matt! This is quite helpful.

    Will this overwrite a measure with the same name on the target instance? I have a client who has 25 clients, each with their own set of charts, but using the same Dynamics365 source. Updating between them has become tedious…

    Lon

  4. @AK, I believe I may have a solution to your issue of not being able to Copy and Paste. There is a good chance that if you have opened Tabular Editor for the first time, you might be trying to copy and paste on a READ ONLY session.

    In order to make edits such as Ctrl + C/V, firstly, within Tabular Editor, go to File -> Preferences. Then, in this new window, make sure you have the Checkbox ‘Allow Unsupported Power Bi Features (experimental)’ Ticked. Click Okay. Your session should now be editable. Let me know if you have any issues.

    I will add, I am not a professional with this software however all I can say is that ticking this option worked for me.
    Hope it helps!

  5. 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?

  6. This worked great for me. And, it is always a good idea to set up these templates so there are consistencies across Power BI Development. Appreciate your work.

    1. Not at this time. You could save the report with a new name and go from there, but I guess you would have done that already if that were an option. Apart from that, you can multiselect all visuals on a page, then copy and paste to another report. But you have to do this one page at a time.

  7. Why not just change the source of the workbook? In either approach, we have to make sure that the column names have to be the same, so why not just create a copy of the current file and change the source from the query editor?

      1. it was never my intention to say that this is the best way to do it. As I said above, “It doesn’t matter how you got to this point – the point is if this is you, there is a hell of a task to move the measures over, one by one.”. But given both of you made the same comment, then that was obviously not clear. I have updated the post for clarity

  8. “There is one big issue with this approach, and that is you have to save the destination file as a template. The implication of this is that you will lose all of your report pages in the destination file.”

    I haven’t observed this behavior – usually the pages and visuals are unaffected by saving as a Template. Am I missing something?

  9. Don’t forget to ask everyone to vote “YES” on the PowerBI improvements suggestion to make it easier to manage DAX functions, it is one of the top ten requested enhancements to PowerBI:

    https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/32863513-measure-management-dax-editor

    “Measure Management & DAX Editor
    Anyone who uses PowerBI long enough has had a PBIX with 50-60 measures and it has become time consuming and tedious to mantain. In Excel you can do a quick find/replace to edit several formulas – in PowerBI you need to select each one individually. An “excel-like” interface for editing measures would save a lot of time! This would take PowerBI to the next level regarding productivity. I’ve prepared a mockup for this as well as a DAX Editor. Let me know what you think.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top