Performance Issue due to Switch Function

[gofy758]

I am having serious performance issue due to this measure. Anyone could help please to have an alternative that might not make the report slow? Just to let you know I am new to dax. thanks 🙂

Measure_NightMare  =

VAR caldate =

    SELECTEDVALUE ( 'Calendar'[DayOfWeek] ) 

RETURN

    SWITCH (

        TRUE (),

        caldate = 6,

            CALCULATE (

                SUMX ( 'Fact_TBL', 'Fact_TBL'[Col1] )

                    - SUMX ( 'Fact_TBL', 'Fact_TBL'[Col2] ),

                'Fact_TBL'[Value_Col] >= 40

                    && 'Fact_TBL'[Value_Col] <= 74

            ),

        caldate = 7,

            BLANK ()

        ,

        CALCULATE (

                SUMX ( 'Fact_TBL', 'Fact_TBL'[Col1] )

                    - SUMX ( 'Fact_TBL', 'Fact_TBL'[Col2] ),

                'Fact_TBL'[Value_Col] >= 38

                    && 'Fact_TBL'[Value_Col] <= 80

        )

    )

[ppm1]

That measure doesn't look that bad, and the suggested improvements should only help. How many rows in your fact table? More importantly, how many distinct values do you have in the Value_Col column? Is it whole number? or decimal?

Pat

[Greg_Deckler]

@gofy758 You might try this:

Measure_NightMare  =
VAR caldate = SELECTEDVALUE ( 'Calendar'[DayOfWeek] )
RETURN
    SWITCH (
        TRUE(),
        caldate<6,
               VAR __Table = FILTER(ALLSELECTED('Fact_TBL'),[Value_Col] >= 40 && [Value_Col] <= 74)
            RETURN
                SUMX ( __Table, [Col1] ) - SUMX(__Table, [Col2] ),
        caldate = 6,
               VAR __Table = FILTER(ALLSELECTED('Fact_TBL'),[Value_Col] >= 40 && [Value_Col] <= 74)
            RETURN
                SUMX ( __Table, [Col1] ) - SUMX(__Table, [Col2] ),
            BLANK ()
    )

[AlexisOlson]

@gofy758 It's not clear to me why this is slow but I've struggled with SWITCH myself. You may want to check out some of the links referenced here.

It's possible rearranging the order of the terms could help if you start with the simplest case. That is:

Measure_NightMare =
VAR caldate =
    SELECTEDVALUE ( 'Calendar'[DayOfWeek] )
RETURN
    SWITCH (
        TRUE (),
        caldate = 7, BLANK (),
        caldate = 6,
            CALCULATE (
                SUMX ( 'Fact_TBL', 'Fact_TBL'[Col1] ) - SUMX ( 'Fact_TBL', 'Fact_TBL'[Col2] ),
                'Fact_TBL'[Value_Col] >= 40
                    && 'Fact_TBL'[Value_Col] <= 74
            ),
        CALCULATE (
            SUMX ( 'Fact_TBL', 'Fact_TBL'[Col1] ) - SUMX ( 'Fact_TBL', 'Fact_TBL'[Col2] ),
            'Fact_TBL'[Value_Col] >= 38
                && 'Fact_TBL'[Value_Col] <= 80
        )
    )

You could also try refactoring it using variables:

NightMare = 
VAR caldate =
    SELECTEDVALUE ( 'Calendar'[DayOfWeek] )
VAR case6 =
    CALCULATE (
        SUM ( 'Fact_TBL'[Col1] ) - SUM ( 'Fact_TBL'[Col2] ),
        'Fact_TBL'[Value_Col] >= 40,
        'Fact_TBL'[Value_Col] <= 74
    )
VAR other =
    CALCULATE (
        SUM ( 'Fact_TBL'[Col1] ) - SUM ( 'Fact_TBL'[Col2] ),
        'Fact_TBL'[Value_Col] >= 38,
        'Fact_TBL'[Value_Col] <= 80
    )
RETURN
    SWITCH (
        caldate,
        7, BLANK (),
        6, case6,
        other
    )