Forum Discussion

MagikJukas's avatar
MagikJukas
Icon for Resolver III rankResolver III
2 years ago

Circular dependency - looking for suggestions

Hello, I built in Excel a model that I wish to replicate in DAX. the problem is that I get Circular depedency in DAX. In Excel I do not get errors because the forumlas use Index and level to calcu...
bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
2 years ago

MagikJukas Instead of using SUMIFS, you can use DAX functions like SUMX or FILTER to iterate over tables and perform calculations.

DAX
RequirementFromAbove =
IF (
Table[Level] = 0,
Table[Qty],
CALCULATE (
SUM ( Table[MissingQty] ),
FILTER (
Table,
Table[Material] = EARLIER ( Table[FatherAbove] ) &&
Table[Index] = EARLIER ( Table[Index] ) &&
Table[Level] < EARLIER ( Table[Level] )
)
)
)

 

DAX
StockAvailable =
MAX (
0,
Table[Stock] -
CALCULATE (
SUM ( Table[Consumption] ),
FILTER (
Table,
Table[Material] = EARLIER ( Table[Material] ) &&
Table[Index] < EARLIER ( Table[Index] )
)
)
)

 

Consumption = Table[RequirementFromAbove] - Table[StockAvailable]

 

MissingQty = Table[RequirementFromAbove] - Table[Consumption]

 

  • MagikJukas's avatar
    MagikJukas
    Icon for Resolver III rankResolver III
    2 years ago

    Hello,

    thanks for your solution. the problem is that it creates a dependency with among the 4 column.

    In Excel you can fix it by writing to sum the values earlier. In DAX it does not work. as soon two column have a potential dependency, you cannot calculate.

     

    I am trying to understand if there are tricks to work around this problem.