cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Optimized DAX code for calculating on different Filter Context

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.

Frequent Visitor

Any Idea?