Select All in slicer

[Anonymous]

Hi ,

I have a requirement in my PBI report.

In a filter panel Month filter ,If I select months it should give me Max of selected month,If i dont select any month it shd give default month as current month -2,third requirement ,If i select all in the filter,it should give me max of month .

I have achieved the first two ,3rd requirement 'select all ',I am not able to achieve ..can anyone give me some suggestions or inputs on this

Here's a query i have used

"SlicerSelection1 =

VAR Months= "Selected Month by :" & " " & FORMAT(MAX(CalendarTable[Max-2]),"Mmm-yy")

VAR MaxMonth= "Month :" & " " & FORMAT(MAX(CalendarTable[Max-Month]),"Mmm-yy")

Var Maxselectedmon=IF(ISFILTERED(CalendarTable[Month Year]),"Selected Month :" & " " & [MaxSelectedMonth],MaxMonth)

RETURN

IF(ISFILTERED(CalendarTable[Default Selection]),"Selected Year :" & " " & CONCATENATEX(VALUES(CalendarTable[Default Selection]),CalendarTable[Default Selection],",",CalendarTable[Default Selection],ASC)) & UNICHAR(10) &

IF(ISFILTERED(CalendarTable[Month Year]),"Selected Month by:" & " " & [MaxSelectedMonth],Months)"

[bhanu_gautam · Accepted solution]

@Anonymous , To achieve the desired result please update dax as below

SlicerSelection1 =
VAR SelectedMonths = VALUES(CalendarTable[Month Year])
VAR MaxMonth = MAX(CalendarTable[Month Year])
VAR DefaultMonth = FORMAT(EDATE(TODAY(), -2), "Mmm-yy")
VAR MaxSelectedMonth = FORMAT(MAX(SelectedMonths), "Mmm-yy")

RETURN
IF (
ISFILTERED(CalendarTable[Month Year]),
IF (
COUNTROWS(SelectedMonths) = COUNTROWS(ALL(CalendarTable[Month Year])),
"Selected Month by: " & MaxMonth,
"Selected Month by: " & MaxSelectedMonth
),
"Selected Month by: " & DefaultMonth
)

[Anonymous]

Super! ! !

let me try 

[Anonymous]

Hi @Anonymous ,

Pls has your problem been solved? If so, accept the reply as a solution. This will make it easier for the future people to find the answer quickly.

If not, please provide a more detailed description, preferably some dummy sample data, and the expected results.

Best Regards,

Stephen Tao

[dharmendars007]

Hello @Anonymous , 

You can modify your query to check if all months are selected and then return the maximum month available. Here’s an updated version of your DAX query that includes logic for the "Select All" scenario

SlicerSelection1 =
VAR Months =
"Selected Month by: " & FORMAT(MAX(CalendarTable[Max-2]), "MMM-yy")

VAR MaxMonth =
"Month: " & FORMAT(MAX(CalendarTable[Max-Month]), "MMM-yy")

VAR Maxselectedmon =
IF(
ISFILTERED(CalendarTable[Month Year]),
"Selected Month: " & FORMAT(MAX(CalendarTable[MaxSelectedMonth]), "MMM-yy"),
MaxMonth)

VAR SelectedMonthCount = COUNTROWS(VALUES(CalendarTable[Month Year]))

RETURN
IF(
ISFILTERED(CalendarTable[Default Selection]),
"Selected Year: " & CONCATENATEX(VALUES(CalendarTable[Default Selection]), CalendarTable[Default Selection], ASC) & UNICHAR(10),
IF(
SelectedMonthCount = COUNTROWS(ALL(CalendarTable[Month Year])),
"Selected Month by: " & FORMAT(MAX(CalendarTable[MaxSelectedMonth]), "MMM-yy"),
"Selected Month by: " & Months))

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN