Move Measures to Another Table in Power BI

The ability to move measures from one table to another table exists in Power BI, However, the process is not familiar for many users. Through my consulting and training work I have explained it to many of my clients. The process is simple and much better than re-creating the measure again and deleting the old one. Let me explain how it is in this post.

Measure in the Wrong Table

In this sample Power BI file, I have a measure for Sales, which mistakenly created under the DimCustomer table;

Nothing, of course, is wrong with having a measure in another table. because measures won’t have different behavior if they are located under an unrelated table. For measures, the calculation would run regardless of the table that they are in. However, for the scenario above, it would make more sense to have the Sales measure and other Sales related measures under the FactInternetSales table. Here is how you can move it;

Move the measure to another table

To move the measure to another table, here are the steps you need to do:

Select the Measure

To select the measure, in the Report or Data tab, click on the measure’s name (not on the checkbox, the checkbox is there to add the measure into a visual). by clicking on the measure’s name, you will see the measure is highlighted;

Measure Tools > Home Table (New Layout of Power BI)

After selecting the Measure, go to the Measure Tools (In the new layout of Power BI), and you will find the Home Table;

Modeling > Home Table (Old Layout of Power BI)

If you are still using the old layout of Power BI (in Power BI report server, or using Power BI without the most recent updates or features), you will find the Home Table under the Modeling tab;

Change the Home Table

Change the home table to the table that you want to be the new location (home) for the measure;

Now the measure should be moved to the new table;

Considerations

Moving a measure from one table to another table is that simple, here are a few considerations, however:

  • Measure calculation or evaluation is independent of the home table. Measures’ evaluation will run regardless of the home table.
  • Some users prefer to keep all their measures in a blank table, called Measure table.
  • Some users prefer to keep their measures under their related tables.
  • You can organizer your measures even better by keeping them under Display Folder.

Video

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.

4 thoughts on “Move Measures to Another Table in Power BI

  • I did tried to move measures from one table to another table when i am using SSAS as Data source(Live Connection) but i couldnt.
    Is it possible to do that.??

    • this method is for measures that you have created in the Power BI Desktop.
      if your measure is coming from SSAS live connection, then you should move it in the SSAS model itself, you can do it using SSDT
      Cheers
      Reza

  • Hi Reza – I recently tried this and when I republished the data model with the measures moved to another table, when I tried to connect an existing report to my test data set, I got an error message indicating that PBI desktop was looking for the measure in the old table. If I remove the measure with the error and then replace it with the exact same measure in the new table, it works. I did not refresh all the data in PBI desktop, so I’m wondering if I need to do that before publishing?

    • Hi Andy
      Sometimes Power BI automatically adds the table name before the measure name when you write DAX expression.
      for example, let’s say you have a measure called XYZ, and it lives in the Table1. if you write a DAX expression like: Table1[XYZ]+2, then moving the measure into another table might cause an error in this expression.
      That is why it is recommended to not have the table name before the measure names.
      Cheers
      Reza

Leave a Reply