Choose either SELECTEDVALUE or force value Performance Issues

[Anonymous]

Hi, 

I'm developing some sales reporting and I'm having some issues with my report being performant. I do have a number of slicers on my report pages but would really like to keep them as it empowers users with report options. Essentially, user will select Period Calculation to display either MTD/QTD/YTD/MAT and then for a comparision period based on the Period Calculation chosen so PM/PFYM/PFQ/PFQTD/etc. and then user also has the ability to switch between sales and margins. For 3 of my slicers I was using Switch statements to change measures based on slicer selections but changed to if statements to see if that would improve performance but it doesn't seem to make any difference. In running perofrmance analyzer DAX is my culprit for load times. 

Another layer is that if a given Month or Fiscal Quarter is not selected I want to force the current month/quarter/etc. so that visuals display the latest values always and then from there users can select to look at whatever periods they want. I've included DAX for how I'm trying to achieve this. It seems that when I have manually selected a month or period in the slicer then it seems to run a lot faster but when no periods are selected on the report performance is significantly inhibited. 

I'm guessing this is due to a comination of having multiple slicers linked with switch statements and also the additional layer for forcing the values in visuals. Is there any better way to acheive the same look and feel that I'm going for and making the report more performant? Appreicate any help.

Thanks!
John

[amitchandak · Accepted solution]

@Anonymous , I have use this code in the past and it worked fast

Switch Period =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())

var _max = if(SELECTEDVALUE(Period[PeriodType],"MTD") = "Yesterday",MAX( 'Date'[Date])-1 , today()-1)
var _min =
SWITCH(SELECTEDVALUE(Period[PeriodType],"MTD"),
"YTD",eomonth(_max,-1*MONTH(_max))+1 , //FY April -March
"FYTD",if( Month(_max) <4 , date(year(_max)-1,4,1) ,date(year(_max),4,1)), //FY April -March
"QTD",eomonth(_max,-1* if( mod(Month(_max),3) =0,3,Month(_max)))+1,
"MTD",eomonth(_max,-1)+1 ,
"WTD", _max -WEEKDAY(_max,2)+1,
"today", today() ,
"Yesterday", today()-1 ,
"Cumm", Minx(ALLSELECTED('Date'),'Date'[Date]),
"Rolling 3", date(Year(_max), month(_max) -3, Day(_max))+1,
"Rolling 6", date(Year(_max), month(_max) -6, Day(_max))+1,
"Rolling 12", date(Year(_max), month(_max) -12, Day(_max))+1,
BLANK())
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

Try if these can help

Switch Period =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max =
SWITCH(SELECTEDVALUE(Period[PeriodType],"MTD"),
"YTD",_max1,
"FYTD",_max1,
"QTD",_max1,
"MTD",_max1,
"This Month",eomonth(_max,0),
"LMTD",date(Year(_max), month(_max) -1, Day(_max)),
"Last Month",eomonth(_max,-1),
"WTD", _max1,
"Cumm",_max1,
"Rolling 3", _max1,
"Rolling 6", _max1,
"Rolling 12",_max1,
"Rolling 7 Day",_max1,
"Yesterday",_max1-1,
BLANK())

var _min =
SWITCH(SELECTEDVALUE(Period[PeriodType],"MTD"),
"YTD",eomonth(_max,-1*MONTH(_max))+1 , //FY April -March
"FYTD",if( Month(_max) <4 , date(year(_max)-1,4,1) ,date(year(_max),4,1)), //FY April -March
"QTD",eomonth(_max,-1* if( mod(Month(_max),3) =0,3,mod(Month(_max),3)))+1,
"MTD",eomonth(_max,-1)+1 ,
"This Month",eomonth(_max,-1)+1 ,
"LMTD",eomonth(_max,-1)+1,
"Last Month",eomonth(_max,-1)+1,
"WTD", _max -WEEKDAY(_max,2)+1,
"Cumm", Minx(ALLSELECTED('Date'),'Date'[Date]),
"Rolling 3", date(Year(_max), month(_max) -3, Day(_max))+1,
"Rolling 6", date(Year(_max), month(_max) -6, Day(_max))+1,
"Rolling 12", date(Year(_max), month(_max) -12, Day(_max))+1,
"Rolling 7 Day", date(Year(_max), month(_max) , Day(_max)-7),
"Yesterday",_max1-1
BLANK())
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

Switch Period =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min =
SWITCH(SELECTEDVALUE(Period[PeriodType],"MTD"),
"YTD",eomonth(_max,-1*MONTH(_max))+1 , //FY April -March
"LYTD",eomonth(_max,-1*MONTH(_max))+1 ,
"FYTD",if( Month(_max) <4 , date(year(_max)-1,4,1) ,date(year(_max),4,1)), //FY April -March
"QTD",eomonth(_max,-1* if( mod(Month(_max),3) =0,3,Month(_max)))+1,
"MTD",eomonth(_max,-1)+1 ,
"LMTD",eomonth(_max,-1)+1 ,
"LYMTD",eomonth(_max,-1)+1 ,
"WTD", _max -WEEKDAY(_max,2)+1,
"Cumm", Minx(ALLSELECTED('Date'),'Date'[Date]),
"Rolling 3", date(Year(_max), month(_max) -3, Day(_max))+1,
"Rolling 6", date(Year(_max), month(_max) -6, Day(_max))+1,
"Rolling 12", date(Year(_max), month(_max) -12, Day(_max))+1,
BLANK())
var _max1 = SWITCH(SELECTEDVALUE(Period[PeriodType],"MTD") ,
"LYTD",Date(Year(_max)-1, month(_max), Day(_max))
"LMTD",Date(Year(_max), month(_max)-1, Day(_max))
"LYMTD",Date(Year(_max)-1, month(_max), Day(_max) ) ,
_max)
var _min1 = SWITCH(SELECTEDVALUE(Period[PeriodType],"MTD") ,
"LYTD",Date(Year(_min)-1, month(_min), Day(_min))
"LMTD",Date(Year(_min), month(_min)-1, Day(_min))
"LYMTD",Date(Year(_min)-1, month(_min), Day(_min) ) ,
_min)
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min1,_max1))

https://medium.com/chandakamit/power-bi-when-i-felt-lazy-and-i-needed-too-many-measures-ed8de20d9f79

[Anonymous]

I figured out my issue was that in my SWITCH statement I was using an index from my slicer tables (disconnected tables) but I used the period name to fitler directly on the report. The DAX optimization for SWITCH requires that you use the column that you're directly filtering on the report. So a very small change lead to huge increase in perofmrance. I might also try your formulas to see if I can squeeze out some more preformance. Thanks.

[Anonymous]

Thank you. I did try this and seems to give some performance boost but analyzing with DAX Studio it would seem that the biggest drag on performance is going through multiple switch statements to control not only which measures I use for the period calculations and comparison period calculations but also which KPI I want to show (i.e. Sales or Margin). There is a big performance drag with the KPI Switch function.