Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Performance Issue due to Switch Function

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 🙂 M...
Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

Anonymous 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's avatar
    AlexisOlson
    Super User
    3 years ago

    Anonymous 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
    )