Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Circular Dependency help

Hi all   have a circular dependency error, it was working and i am not sure of the problem. i added an extra column in the below table for "Region" but my formula's shouldnt care about that consid...
Need Help
  • edhans's avatar
    edhans
    4 years ago

    They were referencing each other. That is what CALCULATE() does in a Calculated Column. It creates a hidden filter for every column in the table. So when you do the first one, it works. But when you do the second one, it create a filter for the first one, but now the first one creates a filter for the second one. 

    But you don't need CALCULATE here. Here are both column formulas, one Calc Min, and the other Calc Max.

    Calc Min = 
VAR varCurrentDiff = Data[Capacity v Usage Difference]
VAR varMin = MIN(Data[Capacity v Usage Difference])
VAR Result = 
IF(
    varCurrentDiff = varMin,
    1,
    0
)
RETURN
    Result

Calc Max = 
VAR varCurrentDiff = Data[Capacity v Usage Difference]
VAR varMax = MAX(Data[Capacity v Usage Difference])
VAR Result = 
IF(
    varCurrentDiff = varMax,
    1,
    0
)
RETURN
    Result

      You can see my Calc Min/Max are the same as your desired result.

     

     

edhans's avatar
edhans
Community Champion
4 years ago

You are manipulating the filter context that CALCULATE applies with ALLEXCEPT, so you told it to remove filters from everything except the difference column. Therefore CALCULATE doesn't create a filter for every column, and then doesn't create the circular reference. 

But as you can see in my formula, you don't need calculate at all. Create a new column and just put =MAX('Difference Table'[Capacity v Usage Difference]) in it and hit ok. It will be the same value on every row, whatever the MAX value is for the 'Difference Table'[Capacity v Usage Difference] column. Put SUM('Difference Table'[Capacity v Usage Difference]). Same thing.

Now put CALCULATE(SUM('Difference Table'[Capacity v Usage Difference])

Now each row will be unique, and the same value as if you had just put ='Difference Table'[Capacity v Usage Difference] (unless any of the rows are 100% identical)

Study up on context transition to really understand how this works. Chapters 4 and 5 of the Definitive Guide to DAX will really help here. I've read those two chapters dozens of times. So much depth there.

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

edhans 

thanks for the added info. the calculated usage was driven from another post i did a while back, good to know that there are alternatives and i have something to work on

 

thanks