Circular dependency - looking for suggestions

[MagikJukas]

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

[bhanu_gautam]

@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]

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.