Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
HamidRezaSajjad
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.

DAX.jpg

 

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.

1 REPLY 1
HamidRezaSajjad
Frequent Visitor

Any Idea?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.