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