Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Select All in slicer

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 curren...
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    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
    )

dharmendars007's avatar
dharmendars007
Icon for Memorable Member rankMemorable Member
1 year ago

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