Forum Discussion
Utterly perplexed why I'm getting error messages when refreshing this model
- 2 months ago
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.
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
- masplin2 months ago
Impactful Individual
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
- FBergamaschi2 months ago
Super User
Hi masplin
I am on it but it is very slow to update PQuery and check the issue
Please give me a cuple of days and in case come back to me via mail to remind me
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
- FBergamaschi2 months ago
Super User
Hi masplin
the problem is some circular reference somewhere
I disabled the load of the query MSOA_Mappping query and now the problem is indicated to be somewhere else
Mu suggestion:
- disable all relationships in Power BI between tables
- try to load queries again
Please let me know if this solves, then you can rebuild the model
I am not sure what is casuing this but at least we know it is not specific to Power Query: it is a problem when the refresh is finished and the model gest updated
Hope I helped
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