Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

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...
@Slicer Date
datesbetween
DAX
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    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
    )
)
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

 

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

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
    )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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

    • FreemanZ's avatar
      FreemanZ
      Icon for Super User rankSuper User
      3 years ago

      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;

      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.