topic Re: Unable to select multiple values in slicer and pass to datesbetween function in DAX Commands and Tips

Unable to select multiple values in slicer and pass to datesbetween function

Anonymous — Wed, 08 Feb 2023 02:42:14 GMT

Hi Everyone,

I need help.

I have a DAX measure which works fine with a single value selected in the slicer but when multiple values are selected this doesn't work as expected. Can someone please guide me? Thank you for the help

Sales New Measure =
VAR SlicerSelection = SELECTEDVALUE('Calendar'[DAY_OF_WEEK_NM])
RETURN IF(SlicerSelection=BLANK(),[Sales Dynamic],CALCULATE(sum(Purchase[Sales]),
DATESBETWEEN('Calendar'[CALENDAR_DT], [Min Date], [Max Date]), 'Calendar'[DAY_OF_WEEK_NM] = SlicerSelection))

Re: Unable to select multiple values in slicer and pass to datesbetween function

FreemanZ — Wed, 08 Feb 2023 03:13:47 GMT

hi Anonymous

try like:

Sales New Measure = VAR SlicerSelection = VALUES('Calendar'[DAY_OF_WEEK_NM]) RETURN IF( SlicerSelection=BLANK(), [Sales Dynamic], CALCULATE( sum(Purchase[Sales]), DATESBETWEEN( 'Calendar'[CALENDAR_DT], [Min Date], [Max Date] ), 'Calendar'[DAY_OF_WEEK_NM] IN SlicerSelection ) )

Re: Unable to select multiple values in slicer and pass to datesbetween function

Anonymous — Wed, 08 Feb 2023 03:21:44 GMT

Thank you FreemanZ for helping. I did the changes but got the following error. Please advise. Thank you

Re: Unable to select multiple values in slicer and pass to datesbetween function

FreemanZ — Wed, 08 Feb 2023 07:02:34 GMT

hi Anonymous

try like:

Sales New Measure = VAR SlicerSelection = VALUES('Calendar'[DAY_OF_WEEK_NM]) RETURN IF( COUNTROWS(SlicerSelection)= COUNTROWS(ALL('Calendar'[DAY_OF_WEEK_NM])) , [Sales Dynamic], CALCULATE( sum(Purchase[Sales]), DATESBETWEEN( 'Calendar'[CALENDAR_DT], [Min Date], [Max Date] ), 'Calendar'[DAY_OF_WEEK_NM] IN SlicerSelection ) )

Re: Unable to select multiple values in slicer and pass to datesbetween function

Anonymous — Wed, 08 Feb 2023 07:09:03 GMT

FreemanZ , You're the Best!! Thank you for all your help.

Re: Unable to select multiple values in slicer and pass to datesbetween function

FreemanZ — Wed, 08 Feb 2023 07:32:33 GMT

hi Anonymous

also learned from your case.

So, in general, when there are multiple results to capture from a slicer, we either

1) use MIN/MAX instead of SELECTEDVALUE, to get the min/max value only;

2) use VALUES instead SELECTEDVALUE. But when nothing is selected, SELECTEDVALUE() returns blank, but VALUES() returns a full list, like ALL(). This not intuitive from the beginning.