Optimized DAX code for calculating on different Filter Context

HamidRezaSajjad — Tue, 12 Sep 2023 08:25:32 GMT

Hello. I've encountered a Sencario in Which I have to calculate each Value in different FC(Filter Context) based on predefiend Scenarios of A and B.

here is my Data Structure in Excel.

in above image, [OldCost] and [NewCost] are two Calculated Columns and are the result of [Last Purchase Rate]*[Standard Consuption Rate] and A and B are two Measures calculated by DAX by the following Code snippet for B:

= VAR test10 = IF( MAX( BothBOM[ItemCode] ) <> "0501-2020", 0, SUMX( SUMMARIZE( FILTER( ALLEXCEPT( BothBOM, BothBOM[ProductCode] ), BothBOM[ItemCode] = "0501-2020" || BothBOM[ItemCode] = "0501-2014" ), "total", CALCULATE( SUMX( BothBOM, BothBOM[NewCost] ), BothBOM[ItemCode] = "0501-2020" ) - CALCULATE( SUMX( BothBOM, BothBOM[OldCost] ), BothBOM[ItemCode] = "0501-2014" ) ), [total] ) ) VAR test9 = CALCULATE( SUMX( BothBOM, BothBOM[OldCost] ), KEEPFILTERS( LEFT( BothBOM[ItemCode], 4 ) = "0502" ) ) VAR test11 = SUMX( SUMMARIZE( FILTER( ALLEXCEPT( BothBOM, BothBOM[ProductCode] ), BothBOM[ItemCode] = "0501-2020" || BothBOM[ItemCode] = "0501-2014" ), "total2", CALCULATE( SUMX( BothBOM, BothBOM[NewCost] ), BothBOM[ItemCode] = "0501-2020" ) - CALCULATE( SUMX( BothBOM, BothBOM[OldCost] ), BothBOM[ItemCode] = "0501-2014" ) ), [total2] ) RETURN IF( NOT ( HASONEVALUE( BothBOM[ItemCode] ) ), test11 + test9, test9 + test10 )

in summary, in senario B (VAR=test10), I have to calculate for ([ItemCode]="0502-2020") as :

([ItemCode]="0502-2020") minus ([ItemCode]="0501-2014") e.i (433.000-146.000=287.000)

and VAR=test11 stands for calculating subtotals in different context.

the outcome of these DAX is as expected, but I am wondering if there might be a better solution and better DAX coding?Additionally , I'm curious about the efficeny of this code. specially for VAR=test11 by which I've handeled subtotals for the Measure.

Re: Optimized DAX code for calculating on different Filter Context

HamidRezaSajjad — Mon, 18 Sep 2023 07:02:16 GMT

Any Idea?

topic Optimized DAX code for calculating on different Filter Context in DAX Commands and Tips

Optimized DAX code for calculating on different Filter Context

Re: Optimized DAX code for calculating on different Filter Context