Circular Dependency on Calculated Column

[StephenK]

I'm knocking my head against a wall because of a circular dependency error I am getting on a calculated column.

I'm trying to add two calculated columns to my provider schedule table. I can add one, but when I try to add the second it throws an error.

1.

COVIDAdjustedSlots =
IF('Provider Schedule'[ResourceId]=********,('Provider Schedule'[COVIDScheduledSlots]-[COVIDAdminDaySlotAdjustment]),
IF('Provider Schedule'[ResourceId]=********,('Provider Schedule'[COVIDScheduledSlots]-[COVIDAdminDaySlotAdjustment]),
IF('Provider Schedule'[ResourceId]=********,('Provider Schedule'[COVIDScheduledSlots]-[COVIDAdminDaySlotAdjustment]),
IF('Provider Schedule'[ResourceId]=********,('Provider Schedule'[COVIDScheduledSlots]-[COVIDAdminDaySlotAdjustment]),'Provider Schedule'[COVIDScheduledSlots]))))

2.

AdjustedSlots = IF('Provider Schedule'[ResourceId]=********,('Provider Schedule'[ScheduledSlots]-[AdminDaySlotAdjustment]),
IF('Provider Schedule'[ResourceId]=********,('Provider Schedule'[ScheduledSlots]-[AdminDaySlotAdjustment]),
IF('Provider Schedule'[ResourceId]=********,('Provider Schedule'[ScheduledSlots]-[AdminDaySlotAdjustment]),
IF('Provider Schedule'[ResourceId]=********,('Provider Schedule'[ScheduledSlots]-[AdminDaySlotAdjustment]),'Provider Schedule'[ScheduledSlots]))))

[COVIDScheduledSlots] and [ScheduledSlots] are columns added through Power Query.

[AdminDaySlotAdjustment] and [COVIDAdminDaySlotAdjustment] are both measures based on Provider Schedule table and the related table Admin_Days

AdminDaySlotAdjustment = (SUM(Admin_Days[AdminDaysPerWk])*(([Workdays]/5)/[Workdays]))*SUM('Provider Schedule '[ScheduledSlots])

COVIDAdminDaySlotAdjustment = (SUM(Admin_Days[AdminDaysPerWk])*(([Workdays]/5)/[Workdays]))*SUM('Provider Schedule'[COVIDScheduledSlots])

Any help is greatly appreciated.

[StephenK · Accepted solution]

So the problem seems to be that my calculated column was referencing a measure which also referenced a column in that same table. 

I found the easiest way to fix the problem was to rewrite the measure AdminDaySlotAdjustment into a calculated column:

IF('Provider Schedule'[ResourceId] IN {******},(.5/5)*'Provider Schedule'[ScheduledSlots],BLANK())

I then changed the calculated columns COVIDAdjustedSlots and AdjustedSlots to reference the calculated column above instead of the original measure I had. 

I don't think this is the ideal solution. Seems more like a quick and dirty fix to get my model to do what I needed it to. If anyone has a more elegant solution I'm all ears.

[jthomson]

Don't know about your problem, but the DAX would be tidier and probably quicker if you do something like if ([ResourceId] in {value1, value2, value3} rather than a huge nested if