trying to optimize dax code

[Anonymous]

I have a working measure which I am trying to optimize. (it takes too long to run…)

I have this measure (which I don’t display...).

Basic:=



VAR _sumat = [Net Revenue] - [Expected Revenue]



VAR _sumlevel2 =

    IF ( _sumat > 0, 0, _sumat )



RETURN

    IF ( ISINSCOPE ( Revenue[Level3] ), _sumat,

    IF ( ISINSCOPE ( Revenue[Level2] ), _sumlevel2 )



)

And this is the one that I do display:

Result:=
VAR _LEVEL1 =
    SUMX (
        SUMMARIZE (
            FILTER (
                ALL ( 'revenue' ),
                Revenue[Level1] = MAX ( Revenue[Level1] ) && Revenue[Level2] IN VALUES (  Revenue[Level2] )
            ),
            Revenue[Level1],
        Revenue[Level2]
        ),
        [Basic]
    )

VAR _LEVEL2 =
    SUMX (
        SUMMARIZE (
            FILTER (
                ALL ( 'revenue' ),
                Revenue[Customer] = MAX ( Revenue[Customer] ) && Revenue[Level2] IN VALUES (  Revenue[Level2] )
            ),
            Revenue[Customer],
        Revenue[Level2]
        ),
        [Basic]
    )





Return

SWITCH (
    TRUE(),
    ISINSCOPE (Revenue[Level3] ), [Basic],
    ISINSCOPE ( Revenue[Level2] ), [Basic],
ISINSCOPE ( Revenue[Level1] ), _LEVEL1,
ISINSCOPE ( Revenue[Customer] ), _LEVEL2
    

)
    

Is there a way to achieve this behaviour faster?

Ps:   Net revenue and expected revenue are just the classic SUM: SUM(Revenue[ExpectedRevenue]),

Ps2: to achieve it I also had to take level3,level2, and level1 and Customer from the Project and Customer dimensions to the Fact using calculated columns like :  =related(Project[Level1])

[ppm1]

Assuming you have 1:M between the Project/Customer and Revenue table (you shouldn't need those calculated columns), please try this version in your visual. Does it get the same results?

NewMeasure =
VAR tSummary =
    ADDCOLUMNS (
        SUMMARIZE (
            Revenue,
            Revenue[Level1],
            Revenue[Level2],
            Revenue[Level3],
            Revenue[Level4]
        ),
        "cBasic", [Net Revenue] - [Expected Revenue]
    )
RETURN
    SUMX ( FILTER ( tSummary, [cBasic] < 0 ), [cBasic] )

Pat