Forum Discussion

Paula1323's avatar
Paula1323
New Member
4 years ago
Solved

MEASUREMENT OPTIMIZATION USING SUMX

  • Hi Paula1323 
    Using variables will speed it a little. However, without further details won't be able to provide you with a definitive answer. Seems like the row level SUMX can be avoided at least the measures inside it can be avoided.

    Generated Reduced PT =
    IF (
        HASONEVALUE ( 'Generated PT'[BK_MISSION] ),
        VAR Case1 = [Case_1]
        VAR Case2 = [Case_2]
        VAR Case3 = [Case_3]
        VAR Case4 = [Case_4]
        VAR Case5 = [Case_5]
        VAR Case6 = [Case_6]
        RETURN
            SWITCH (
                TRUE (),
                Case1 > 0, Case1,
                Case2 > 0, Case2,
                Case3 > 0, Case3,
                Case4 > 0, Case4,
                Case5 > 0, Case5,
                Case6 > 0, Case6
            ),
        CALCULATE (
            SUMX (
                'Mission (Attributes)',
                VAR Case1 = [Case_1]
                VAR Case2 = [Case_2]
                VAR Case3 = [Case_3]
                VAR Case4 = [Case_4]
                VAR Case5 = [Case_5]
                VAR Case6 = [Case_6]
                RETURN
                    SWITCH (
                        TRUE (),
                        Case1 > 0, Case1,
                        Case2 > 0, Case2,
                        Case3 > 0, Case3,
                        Case4 > 0, Case4,
                        Case5 > 0, Case5,
                        Case6 > 0, Case6
                    )
            ),
            FILTER ( 'Mission (Attributes)', 'Mission (Attributes)'[Company] = "PT" )
        )
    )

2 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion

    Hi Paula1323 
    Using variables will speed it a little. However, without further details won't be able to provide you with a definitive answer. Seems like the row level SUMX can be avoided at least the measures inside it can be avoided.

    Generated Reduced PT =
    IF (
        HASONEVALUE ( 'Generated PT'[BK_MISSION] ),
        VAR Case1 = [Case_1]
        VAR Case2 = [Case_2]
        VAR Case3 = [Case_3]
        VAR Case4 = [Case_4]
        VAR Case5 = [Case_5]
        VAR Case6 = [Case_6]
        RETURN
            SWITCH (
                TRUE (),
                Case1 > 0, Case1,
                Case2 > 0, Case2,
                Case3 > 0, Case3,
                Case4 > 0, Case4,
                Case5 > 0, Case5,
                Case6 > 0, Case6
            ),
        CALCULATE (
            SUMX (
                'Mission (Attributes)',
                VAR Case1 = [Case_1]
                VAR Case2 = [Case_2]
                VAR Case3 = [Case_3]
                VAR Case4 = [Case_4]
                VAR Case5 = [Case_5]
                VAR Case6 = [Case_6]
                RETURN
                    SWITCH (
                        TRUE (),
                        Case1 > 0, Case1,
                        Case2 > 0, Case2,
                        Case3 > 0, Case3,
                        Case4 > 0, Case4,
                        Case5 > 0, Case5,
                        Case6 > 0, Case6
                    )
            ),
            FILTER ( 'Mission (Attributes)', 'Mission (Attributes)'[Company] = "PT" )
        )
    )