Forum Discussion
Power ParticipantUtterly 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.
Community SupportHi 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 Srikanth
masplinThat 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