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 calculate in a given order, for instance:

Requirement from Above=IF([@Level]=0,[@Qty],SUMIFS([Missing Qty],[Material],[@[Father Above]],[Index],[@Index],[Level],"<"&[@Level]))
Stock available=MAX(0,[@Stock]-SUMIFS([Consumption],[Material],[@Material],[Index],"<"&[@Index]))

Consumption is the difference between those two fields, and missing Qty is requirement- consumption.

 

How can I solve it in DAX?

clearly, it returns error as soon as two fields are linked to each other, ignoring the fact the formula structure avoids dependency at the same level.

 

regards

2 Replies

  • 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.