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 🙂

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

3 Replies

  • 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
    )
  • ppm1's avatar
    ppm1
    Solution Sage
    3 years ago

    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