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
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
14 | |
12 | |
12 | |
9 |
User | Count |
---|---|
63 | |
22 | |
21 | |
17 | |
11 |