Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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))
Solved! Go to 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
)
)
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
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
)
)
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
12 | |
11 | |
8 | |
7 | |
7 |
User | Count |
---|---|
20 | |
14 | |
11 | |
10 | |
10 |