Forum Discussion
Utterly perplexed why I'm getting error messages when refreshing this model
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.
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?
