Forum Discussion
Utterly perplexed why I'm getting error messages when refreshing this model
I've spent 3 days on this tryingh everything and cannot get past this so hoping someone cleverer than me can explain what is happening.
I have a complicated model that maps demopgraphic data for the UK such as age and income onto the UK MSOA/LA geography. In 2021 the UK revised all of these mappings getting rid of som eareas and creating new ones or merging them. Last time i did this refresh most of the data was still on the old 2011 classification, but some of it was on the 2021 classification. I therefore chose to use the 2021 basis for my modle and wrote some code to map the 2011 basis data to the 2023 basis.
I just came to do an update and now all the data is on the 2021 basis. The problem is if i replace my table MSOA_Mapping directly with some new code, which generates exactly the same column names and has exactly the same unique identifier (MSOA Code) it throws and error basically saying every column in the table doesnt exist or has no relationship in this context. I am 100% usre the column names in Power Query are identical and there the same number of rows so I dnt have an orphaned or null items breaking the relationships.
To get round this I have taken my old table, thrown away all the data except the unique identified column (MSOA code) then merged it with the new data. This works fine, but seems daft.
The model is quite big but it is here Demographic model
MSOA_Mapping is the table I need and MSOA_Mapping (2) is the new definition of this table. I have added a check column on each to confirm the rows are identical.
If you go to power query and copy the code from MSOA_Mapping (2) over the MSOa_Mapping query it produces the same result (I think). However if you then refresh it will error.
Driving me a bit nuts as seems illogical to me, but maybe some subtle issue i dont understand.
I tried turning all the relationships to inactive as previously that has helped resolve where the issue is, but not this time
Appreciate any insight as to what is causing this as stuck keeping the old data
Hi masplin
Please check the below points.
IncomeGroup[Min]/[Max] call the measures [Total Population] and [Av annual Net Income], which scan all of MSOA_Mapping including [Income Group]/[Income Tgt], which are built from Min/Max. That loop is what breaks the rebuild.Three quick edits fix it:
1. Add a plain column: MSOA Population = CALCULATE ( SUM ( 'Age by MSOA'[Population] ), 'Age by MSOA'[Year] = 2024 )
2. In Min and Max, replace the AVIncome VAR (drop the measures):
VAR AVIncome =
DIVIDE (
SUMX ( MSOA_Mapping, MSOA_Mapping[MSOA Population] * MSOA_Mapping[Net annual income after housing costs (£)] ),
SUMX ( MSOA_Mapping, MSOA_Mapping[MSOA Population] ),
BLANK ()
)
Keep the rest of Min/Max unchanged same result.
3. Set MSOA_Mapping[LA Name] → Property[LA_name] to single-direction (keep it Active).Note: never call a measure inside a calculated column on a table that measure scans.
Make those three changes and refresh sorted.
Thank you.
25 Replies
Ok I have put al lthe supporting files in one folder here Source Files .
I editted the query so there is a parameter you can change to point it at wherever you down load the source files to so hopefully it will all work.
much appreciated any thoughts
OK I am on it, downloading
you said you changed the query with the parameter but I do not see the updated pbix on the shared link you just sent, can you share the new pbix as well?
Thanks
Hi masplin
I succesfully refreshed after drivig the parameters to my local copy of the input files. Weird!
I cannot attach the refreshed pbix due to size limits, if you send me a link to a sharepoint or something like that I can return the file
Maybe you want to try again and see if the error persists? I did not get any
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Best
Hi masplin
Please check the below points.
IncomeGroup[Min]/[Max] call the measures [Total Population] and [Av annual Net Income], which scan all of MSOA_Mapping including [Income Group]/[Income Tgt], which are built from Min/Max. That loop is what breaks the rebuild.Three quick edits fix it:
1. Add a plain column: MSOA Population = CALCULATE ( SUM ( 'Age by MSOA'[Population] ), 'Age by MSOA'[Year] = 2024 )
2. In Min and Max, replace the AVIncome VAR (drop the measures):
VAR AVIncome =
DIVIDE (
SUMX ( MSOA_Mapping, MSOA_Mapping[MSOA Population] * MSOA_Mapping[Net annual income after housing costs (£)] ),
SUMX ( MSOA_Mapping, MSOA_Mapping[MSOA Population] ),
BLANK ()
)
Keep the rest of Min/Max unchanged same result.
3. Set MSOA_Mapping[LA Name] → Property[LA_name] to single-direction (keep it Active).Note: never call a measure inside a calculated column on a table that measure scans.
Make those three changes and refresh sorted.
Thank you.Absolutely brilliant really appreciate your support. Out of interest is there somewhere specific you looked in pbib file to know this was the problem or just experience?
I didnt change the property relationship, but other changes fixed it. So ideally calculate everything you can in power query to avoid these issues. Secondly try not to use measures inside a calcuated column if that column is then part of another measure?
What is frustrating is you can add all this stuff without generating any errors or warnings until later you try and do a full refresh so the model can get quite out of control.
Hi. I'll try this but i dont think the last point is possible. Essentially MSOA Mapping is my core geographic table and LA name is duplicated because MSOA is a lower level of granularity. The Property data is only available at LA level. So I need the MSOA Mapping (Many) to filter the Property data (One) hence I chose both because single is property filtering MSOA mapping isnt it?
Hi masplin
in order to help you, I need to see the Power Query errors in Power Query itself
Point is that your pbix (thanks for sharing) points to local file so I cannot open Power Query and see anything
Is there a chance that you can load not only the pbix but also the source files somewhere and reshare?
Best
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
ah didnt think of that!!! There are no eriors in power query only when i try to refresh. Let me try and create a folder with everything in it
One i sent you works. The issue is if you replace the query in MSOA_Maping with the one i weant in MSOA_Mapping (2) it errors. You can see the current query is using the old query, throwing away the data then merging it will the new data so effectively keepinhg the list of MSOA Code which is the unique identifier. I can send you a version that doenst work?
Yes
Please add images of the error so I reproduce it
Thanks
So all I have done is copied the query MSOA_Mapping (2) and pasted it into the MSOA_Mappping query. I think the data is identical but gives this error
The broken version of the file is here Broken Version
Thanks
Hi masplin
If possible please upload the PBIX unzipped as a Power BI Project (.pbip) or export the model metadata via Tabular Editor's Model.bim or TMDL, We will check the semantic model and will find which object is causing the failure.
Thanks
C Srikanth.Hi.
I have saved the PBIP here
As i mentioned this version only works because i have retained the old MSOA mapping query which is based on #"MSOA Mapping 2023 2011 basis" removed all the columns except the MSOA code which is the unique indetifier and then merged with the new version based on #"MSOA Mapping 2023 2021 basis". As far as i can tell there are exactly the same MSOA codes just different data columns. If i try to use the 2021 basis as the start of the query it throws all these errors.
FBergamaschi helpfuilly went through the model, but all he manged to prove is some of the calcauted columns cause circuilar references if you delete the relatioinships, but no idea which one. There are no ciruclar relationship warnings in the current model.
do you need me to put all the supporting files in the same folder so you can perform a refresh?
Thanks for your help
Hi masplin
Thanks for sharing the PBIP. I have opened the model and this isn't a data problem, so you were right to be puzzled. FBergamaschi was mentioned above as it is a circular dependency, and I can point to exactly where it comes from.
Inside MSOA_Mapping you have three calculated columns that reference measures:- MSOA Population = [Total Population]
- MSOA Rank = ROUND([AVG MSOA Rank VC], 2)
- Net Income LA (uses [Total Population] with EARLIER
Here is the issue: when a calculated column calls a measure, it quietly depends on every column in the table because the engine can't know which ones the measure might use. And those measures scan MSOA_Mapping right back (via Age by MSOA and the SUMMARIZE / AVERAGEX over MSOA_Mapping).
Add your bidirectional relationship MSOA_Mapping[LA Name] ↔ Property , and the loop closes — the table ends up depending on itself.That's also why Power Query looks fine but refresh fails: the dependency graph is only rebuilt on refresh. Your old-query-merge trick works because it keeps the existing columns intact instead of forcing a full rebuild which is why swapping in the clean 2021 query trips it.
To fix it properly as a workaround please below follow the below points:1. Move MSOA Population and Net Income LA into Power Query as real columns
(calculate the LA weighted-average income with Table.Group ), or keep them as measures only don't store them as columns.
2. Keep MSOA Rank as a measure (it rides on the whole scoring chain, so it shouldn't be a column).
3. Switch the MSOA_Mapping ↔ Property relationship to single-direction
(use CROSSFILTER in a measure if you still need that filter path).Fixing just Net Income LA will likely clear it on its own, but doing all three keeps the model clean. The rule of thumb: aggregations that scan a table should stay as measures, and per-row values belong in Power Query, never mix a measure into a calculated column on the same table it scans.
Give that a try and let me know how it goes happy to help rework the query if useful.Thanks,
C SrikanthThat is very helpful
So I deleted the MSOA rank column as not needed (also from Providers table)
I added a column to MSOA mapping query for both MSOA Popuation ans Net income LA
I also change a column calc in IncomeGroup that was leaning on the previous MSOA Population which it didnt need to
However same failure
I switch the property relationship to inactive different error
I have put the model and PBIP all here Care Demographics as no idea what to do next
Thanks for your help
Hi masplin
Thank you for letting us know that your issue has been resolved.
If you have any issues related to PowerBI and Fabric, do reach out to community.
We are happy to assist you.
Thanks
Srikanth Cheri
CST Team.
