Modeling tool and moving measures to another table

[Anonymous]

The latest version of PBI desktop includes a feature in the modeling tool to move measures. It works pretty well, but takes a while if you have a lot of measures. I tested this new feature in desktop, and everything was looking good, but when I went to connect an existing report to the test dataset, all of the visuals displayed errors saying that the measure can't be found. I thought since measures didn't explicitly reference the table like columns do, that this would not be an issue. Has anyone else tried this? I didn't move any of the columns that the base measures reference. It seems like now I'm going to have to go back to every single PBIX file and remove and replace ALL of the measures. I really don't want to have to do that....

[Greg_Deckler]

@Anonymous - Are you referring to changing the measure's Home table under Measure tools? That has been around for a long time. The issue you can run into doing this is that if you did not explicitly reference the table when referencing a column, then that can cause the measure to not be able to find that column, so you should do this:

SUM('Table'[Column])

Not

SUM([Column])

[Anonymous]

Thanks so much for the replies so far. In all base measures I reference the table and column as in Greg's example below. I moved the measures using the drag and drop feature in the modeling tool. None of the meaures built using base measures references any table (I had this in some of my measures before, but I fixed). I published the model and opened my existing report and changed the data souce, and everything errored. I did not mistakenly change any of the names of the measures.

It seems like the PBIX file is still trying to reference the old table in which the measure lived ("Measures Forecast & Sales Detail"). I moved it to a table called Standard Measures. Interestingly enough, when I refreshed an Excel file I had connected to the model, I did not get any error messages. I'm going to try republishing, and then maybe delete the test model and publish again.

[Greg_Deckler]

@Anonymous - I'm wondering if this is some sort of caching issue going on. I have moved measures between tables in the past and never had a hiccup.

[Anonymous]

Update: I added a few measures back to the original table they were in and republished. This time the measures worked in the existing report. Then I changed the name of the original table and republished. This time the existing PBIX report did NOT work. It could be a caching issue. It seems like the PBIX file is holding on to the old table name or something.

[Anonymous]

Update 2: The only thing that works so far is if I rename my new measure tables to the old names. 

I'm really flumoxed by this. It almost seems like the model itself is forcing the measures to be dependent on the original table name. Even if I publish the model from scratch - ie nothing should be cached - it still thinks the measures need the original table name. I also connected to DAX Studio and looked at some measure dependencies, and it defines the measure with the new table in quotes - 'Standard Measures'[measure name], not with the old table. I've checked and I am definitely referencing table and column names when calculating base measures. 

[v-shex-msft]

Hi @Anonymous,

It sounds like the new structure referenced of data model calculated fields has a conflict with existed old structure at the power bi services side.

I guess they may related to power bi service and some backend processing for furthering sharing and permission management. (power bi service seems to protect and keep the old structures and prevent you directly replace these structures and references)

Power BI Security#data-storage-security 

If you haven't further used/shared these contents, you can try to remove the old one and use the last version to replace it.

BTW, I'd like to suggest you open a ticket to contact power bi team to check the backend process and reasons. (support ticket is free for pro and above license)

submit a support ticket

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks Greg! That could be it. I'll try a few things and check back again later. Maybe refresh the data set on PBI.com as well.

[amitchandak]

@Anonymous , As far as I know, measure belongs to the model. So you should be able to move those around. In fact, measure tables are created to better organize those.

Just check if there rename happened by mistake while moving

Measure Management

https://youtu.be/b9oDIL8nbu0