Forum Discussion

MITeam's avatar
MITeam
Frequent Visitor
1 year ago

Circular Dependency Hierarchy Lookup

Hi. I have a multi source report that is all linked together via a cost centre hierarchy to bring in Site / division etc. so one set of slicers updated income data and fill rate data etc.

 

One of the sources has gaps in cost centres so I have added a formula to fill in those gaps from the hierarcy but because of this I cant use this column as the relationship to the hierarchy to link in to all other sources.

 

Is there a way round this or do I need to pull in the hierarchy twice and use one for the gaps and one for the links? Any super duper ideas?

 

___Cost Code 2 =
SWITCH(TRUE(),
'Fill Rates'[Cost Code] > 0 , 'Fill Rates'[Cost Code],
CALCULATE(
    FIRSTNONBLANK('-Cost Centre Hierarchy'[Cost Code],1),
    FILTER(ALL('-Cost Centre Hierarchy'), '-Cost Centre Hierarchy'[Site Name] = 'Fill Rates'[Site Name])))
Need Help

1 Reply

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    Consider implementing this in Power Query instead of as a calculated column.