topic Re: Performance Issue due to Switch Function in DAX Commands and Tips

Performance Issue due to Switch Function

Anonymous — Fri, 30 Dec 2022 21:12:56 GMT

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

)

Re: Performance Issue due to Switch Function

Greg_Deckler — Fri, 30 Dec 2022 22:10:13 GMT

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

Re: Performance Issue due to Switch Function

AlexisOlson — Sat, 31 Dec 2022 20:45:44 GMT

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 )

Re: Performance Issue due to Switch Function

ppm1 — Sun, 01 Jan 2023 19:22:52 GMT

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