topic Circular dependency - looking for suggestions in DAX Commands and Tips

Circular dependency - looking for suggestions

MagikJukas — Wed, 28 Aug 2024 11:07:45 GMT

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

Re: Circular dependency - looking for suggestions

bhanu_gautam — Wed, 28 Aug 2024 12:38:57 GMT

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]

Re: Circular dependency - looking for suggestions

MagikJukas — Wed, 28 Aug 2024 14:24:01 GMT

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.