Forum Discussion
ctaylor
Helper III
4 years agoIF Statement Wasting Calculation Time on Measure When Condition NOT Met
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 t...
Whitewater100
Solution Sage
4 years agoHi:
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
Helper III
4 years agoThanks 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