Forum Discussion
Circular Dependency help
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 ResultYou can see my Calc Min/Max are the same as your desired result.
hi edhans
i appreciate the reply
the date or table is as follows
with the columns in red as my desired output
| Date | Product | Service | Region | Tested Capacity | Current Usage | Capacity v Usage Difference | Is Min | Is Max |
| 1/09/2021 | Product 1 | Service 1 | Region 1 | 11880000 | 1973899 | 9906101 | 0 | 0 |
| 1/09/2021 | Product 1 | Service 1 | Region 2 | 43956000 | 1973899 | 41982101 | 0 | 1 |
| 1/09/2021 | Product 1 | Service 2 | null | 5280000 | 5633456 | -353456 | 1 | 0 |
| 1/09/2021 | Product 1 | Service 3 | null | 15681600 | 2286873 | 13394727 | 0 | 0 |
| 1/09/2021 | Product 1 | Service 4 | null | 29700000 | 15411644 | 14288356 | 0 | 0 |
| 1/09/2021 | Product 1 | Service 5 | null | 10560000 | 1694191 | 8865809 | 0 | 0 |
| 1/09/2021 | Product 1 | Service 6 | null | 43956000 | 5692197 | 38263803 | 0 | 0 |
| 1/09/2021 | Product 1 | Service 7 | null | 10560000 | 1550133 | 9009867 | 0 | 0 |
essentially i want to mark the isMin column of which item in the "Capacity v Usage Difference" column is the lowest figure
and then i wish to do the opposite in isMax column of which item is the largest figure
from there i wish to use a dynamic filter(which i can do on another column) on the report so if i select it, it will show the lowest figure row in the related graphs and tables
being a measure this will not allow the graph to filter, so i need the IsMin and isMax to be columns so i then can filter on each of those dynamically.
prior the isMin formula was giving me the error, then all of a sudden it changed to the isMax formula. i cannot tell where the circular dependency is coming from as the formulas do not reference each other.
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
ResultYou can see my Calc Min/Max are the same as your desired result.
thanks
that worked
i also found that if i change part of my formula (possibly by luck using allexcept) it worked as desired, but i think your method is a bit more robust
Is Min =VAR minValue =CALCULATE ( MIN ( 'Difference Table'[Capacity v Usage Difference] ), ALLEXCEPT('Difference Table','Difference Table'[Capacity v Usage Difference] ))VAR currentValue =MIN( 'Difference Table'[Capacity v Usage Difference] )RETURNIF ( currentValue = minValue, 1 , 0 )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.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