Forum Discussion

StephenK's avatar
StephenK
Resolver I
6 years ago
Solved

Circular Dependency on Calculated Column

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.
 
 

 

 
Need Help
  • StephenK's avatar
    StephenK
    6 years ago

    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.

2 Replies

  • jthomson's avatar
    jthomson
    Solution Sage
    6 years ago

    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

  • StephenK's avatar
    StephenK
    Resolver I
    6 years ago

    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.