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

[mith123]

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))

[FreemanZ]

hi @mith123 

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
    )
)

[mith123]

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

[FreemanZ · Accepted solution]

hi @mith123 

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
    )
)

[mith123]

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

[FreemanZ]

hi @mith123 

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;

or

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.