Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
Any Idea?
User | Count |
---|---|
20 | |
18 | |
17 | |
11 | |
7 |
User | Count |
---|---|
29 | |
28 | |
13 | |
12 | |
12 |