Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
mith123
Frequent Visitor

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

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

View solution in original post

5 REPLIES 5
FreemanZ
Super User
Super User

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

 

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

mith123_0-1675826466762.png

 

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

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

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. 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.