Copy Measures Between 2 Power BI Files

I have often been asked (and wondered myself) 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.

Edit 12 Feb 19:  Note, I am not saying this is how you should 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 currently the easiest way to do it.  If you have 1 workbook and you want to create a new variant of essentially the same workbook, simply select File\SaveAs and create a copy of your master and go from there.

Recently someone (maybe Gilbert at fourmoo.com?) suggested that it could be done using Tabular Editor.  I couldn’t work out how to do it myself so I asked Marco Russo and he explained it to me.  This article describes how to do it yourself.

Warning!

Ok, here is the warning.  This is not supported by Microsoft.  If you do this and it breaks your model, you will not get support from Microsoft (or me for that matter).  So, back everything up and keep the backups – don’t delete them.  Consider yourself suitably warned 🙂 .

How Do I Do it Already?

Create Templates of your 2 workbooks

The first step involves creating templates of your 2 workbooks.  A template file contains all the structure of the data model including the connection strings to the data, but none of the data itself, nor the reports.  You can later create a new workbook from this template file.

  • Open the workbook that contains the measures (I will refer to this as source.pbix)
  • Save the file as a template File\Save As\Power BI Template File.pbit
  • Open the other PBIX file – the one to which you want to move the measures.
  • Save this file as a template too.

Open Tabular Editor (Twice)

  • Download Tabular Editor https://www.sqlbi.com/tools/tabular-editor/
  • Open an instance of Tabular Editor.
  • Connect to the source template file.
  • When you do this, you will get a warning like this.  Remember my warning above?!

image

  • Open another instance of Tabular Editor and connect to the destination template file.

You should now have 2 instances of Tabular Editor open, one connected to each template file.

Copy the Measures

First you have to make the measures in the source template visible.

  • 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.  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)
  • Save the template

Open the Destination Template

Now the measures have been copied it is simply a matter of opening the destination template in Power BI Desktop.  You can either double click on the template file or open it from the file from the file menu (select templates as the file type).  I will refer to this file as the new destination workbook.

You should be able to see all the measures in the new 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.

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.

 

 

Share?

Comments

  1. 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.

  2. “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?

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

    • I was thinking the same thing. This is a great article but I think your solution is easier and more reliable.

      • 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

    • 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.

Leave a Reply