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

4 Replies

  • dharmendars007's avatar
    dharmendars007
    Memorable 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 

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    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
    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Super! ! !

       

      let me try 

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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