Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Choose either SELECTEDVALUE or force value Performance Issues

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 empo...
  • amitchandak's avatar
    3 years ago

    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