Copy Measures Between 2 Power BI PBIX Files - Excelerator BI

Copy Measures Between 2 Power BI PBIX 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 copy 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 copy measures from one PBIX to another, 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 copy measures between PBIX files?

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.  This is only needed for older versions of Desktop. As of about Sept-2020, this feature is now generally available, so you don’t need to turn it on from preview features. The Report Server version may be different.

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 PBIX files 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.

52 thoughts on “Copy Measures Between 2 Power BI PBIX Files”

  1. Thanks for this post.
    I’ve done it in the past , and I had a massive issue. My model was a live connection to Azure Analysis services database. When I pasted the measures, I expected them to be added to my Power BI file only.
    Instead they were added to the tabular model itself , which was shared among many people.

  2. When the Power BI report connects to an SSAS database then the measures that are shown in Tabular Editor are the measures defined in the SSAS database, not the measures that are defined in the Power BI report.

    So unfortunately I cannot use this method to copy all the Report Level Measures into an SSAS database.

  3. Hi, and thanks for the explanation on Measures.
    I want to run the transfer, but the measure is referring to the Power BI inner created columns. To make things even more exciting – I have two data sets, each has a created (generated) column for line ID (JOIN operator). Then the measure is workig with the created column in 1st data set and column in 2nd data set via RELATED DAX function.

    I thought I could transfer the columns from the 1st Data set in a way you describe Measure’s transfer, but I failed.

    Is there anything I can do to transfer newly added columns from 1st Data set to the 2nd?

  4. We’re trying to develop self service at my organization… I have one sandbox PBIX created by a trainee that has measures that we don’t want in our core data model. I recreated a “thin report” which connects to our core data model and want to copy his sandbox measures to this “thin report”. I would rather not copy each measure one by one. I tried your tabular editor trick but I get an error “”Error connecting to database – The remote server returned an error:(401) Unauthorized” when I try to run tabular editor on the Thin model – I assume because it doesn’t have its own native model. Any workaround for this? Or another method to do bulk copy of measures??

    1. Yes, there is no model in a thin workbook and hence nothing to copy. You could use my local host workbook here https://exceleratorbi.com.au/measure-dependencies-power-bi/ You can use this to extract all measures from the primary workbook. There will still be a cut and paste into the thin workbook, but at least half the job is done. Another option is to
      1. duplicate the main workbook
      2. delete all the measures that you don’t want in there from the main workbook.
      3. in the copy, delete all measures in the main workbook only leaving those that are needed in the thin workbook. Move these into a temp table (so you don’t accidentally delete them).
      4. delete all the tables in the workbook.
      5. connect to the online workbook with get data\power bi dataset

  5. Very useful! Thanks.
    It saved tons of time.
    I got into the situation because two things happened at the same time:
    1. I modified the script I was using to pull data from the database directly to include new columns
    2. At the same time, IT changed the database and user settings.
    Refreshing data became a challenge and I had to start from scratch since the real problem could not even be diagnosed.

  6. Matt,
    Excellent. Surprisingly for me, in one reading of the article I followed the logic. Admittedly I have not done this operation yet, but this is so good to know. This may entice this 71 year old Power BI learner to expand my horizons into Tabular Editor and other external tools like DAX Studio ( I have found another blog of yours on this). I came away with new knowledge and a feeling of encouragement after reading your blog. Thank you!

  7. Hello Matt
    Thank you for this usefull article.
    I would like to ask if you we can do the same with Manage rules ? I have a lot of rules in the first PIBX that have to be the same in the second PIBX and I wouldn’t want to write them again one by one

    Thank you ! Have a nice day !

    1. And in my power bi desktop, I don’t have the line : ” Store datasets using enhanced metadata format”
      Can you explain what does it do ?

      Thank you

      1. I tried to copy paste the Manage Role and it’s impossible. It says that the role to copy points to a table which doen’t exist

        1. Hello Matt
          for the test, I used the the lastest version of PowerBI Desktop that I downloaded yesterday and there is not the preview setting. But next week, I have to use Report Server version. Do you think it could work ?

          1. You are right. This is no longer a preview feature – it is now generally available, so that setting is no longer there. I don’t know about the report server version – sorry. All I know is that it lags the main version

            1. Yes. Do you think my request is possible ? Copy Paste all Roles created in Manage Role from a PIBX to another

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

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

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

  11. @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!

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

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

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

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

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