IF Statement Wasting Calculation Time on Measure When Condition NOT Met

[ctaylor]

Hello,

I have come across a curious issue and was looking for some guidance/help. I am working on some financial data that is being given to me in a way where I am having to create a who lookup table using PATHITEM type commands to build a hierarchy.  Because of this measures do take a little time to compute (about 8-10 seconds for a couple million records).

So, as I move to create projections based on past year averages of values, I am attempting to create an IF or SWITCH structure that reduces the number of calculations down to the lowest possible amount to save visual refresh time by leveraging some toggle switches on the report page for Budget or Trend and a toggle to change the number of past years considered in creating the forecasted average.

I found a curious issue though.

If I create a very simple:

IF(
calc_type = 1,
1,
2,
2
)

The compute time is obviously instant.

But, if I set the toggle that controls calc_type to 2 and change the code to:

IF(
calc_type = 1,
[do measure],
2,
2
)

 I would expect that the return would still be almost instant because the calc_type is not 1, therefor the measure should not calculate but in fact the measure does calculate because now it takes 8-10 seconds to output the number 2.

Why is time being wasted by the engine to calculate a measure when the condition to evaluate the measure has not been met? 

I did find that when I reverse the order and put the 2 condition above the 1, the output is instant again. But if I leave the conditions flipped (2 then 1 instead of 1 then 2) and add both measures:

IF(
calc_type = 2,
[do measure 2],
1,
[do measure 1]
)

 It takes twice as long to evaluate the output which tells me that indeed both measures are being evaluated even though the condition for 1 is not met.

Does anyone know why this is happening or have any tips to ensure that 1 and only 1 measure is being calculated instead of calculating those where the condition is not met?

Thanks!

[Whitewater100]

Hi:

You may want to look into COALESCE function. It provides efficiences in these types of situations. 

https://www.sqlbi.com/articles/the-coalesce-function-in-dax/

[ctaylor]

Thanks @Whitewater100 I will look into this a little further tomorrow because I'm not immediately seeing how this will fix my situation. If it helps, here is the current version I am using which takes about 25 seconds to return a result. I am shooting to get it down to about 10, and I think that it's the extra evals that is adding the extra time. My current product is using Switches, though I found that the switch produces the same strange query timings as the sample IF statement I described in my OP.

YTD Forecast by Time and Range Selections = 

VAR forecast_range = SELECTEDVALUE(Selector_Forecast_Range[Index])
VAR calc_type = SELECTEDVALUE(Selector_Forecast_Type[Index])
var factor = 1+SELECTEDVALUE('Forecast % Change'[Forecast % Change])/100

VAR LY_Budget = CALCULATE(TOTALYTD([Projected CorpActual by Budget], 'Calendar'[Date]), SAMEPERIODLASTYEAR('Calendar'[Date]))
VAR LY_Trend = CALCULATE(TOTALYTD([Projected CorpActual Run Rate], 'Calendar'[Date]), SAMEPERIODLASTYEAR('Calendar'[Date]))

VAR TWOY_Budget = CALCULATE(TOTALYTD([Projected CorpActual by Budget], 'Calendar'[Date]), DATEADD('Calendar'[Date], -2, YEAR))
VAR TWOY_Trend = CALCULATE(TOTALYTD([Projected CorpActual Run Rate], 'Calendar'[Date]), DATEADD('Calendar'[Date], -2, YEAR))

VAR THREEY_Budget = CALCULATE(TOTALYTD([Projected CorpActual by Budget], 'Calendar'[Date]), DATEADD('Calendar'[Date], -3, YEAR))
VAR THREEY_Trend = CALCULATE(TOTALYTD([Projected CorpActual Run Rate], 'Calendar'[Date]), DATEADD('Calendar'[Date], -3, YEAR))

VAR RESULT =
SWITCH(
    calc_type,
    1,
    SWITCH(
        forecast_range,
        1, LY_Budget,
        2, DIVIDE(LY_Budget+TWOY_Budget,2),
        3, DIVIDE(LY_Budget + TWOY_Budget + THREEY_Budget, 3)
    ),
    2,    
    SWITCH(
        forecast_range,
        1, LY_Trend,
        2, DIVIDE(LY_Trend+TWOY_Trend,2),
        3, DIVIDE(LY_Trend + TWOY_Trend + THREEY_Trend, 3)
    )
)

RETURN
RESULT * factor

[tamerj1]

@ctaylor 

I will give it a try. I want to do that becuase I allways use IF to limit the calculation only when needed and I allways obtain better performance.