Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
r-e
New Member

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)"
1 ACCEPTED SOLUTION
bhanu_gautam
Super User
Super User

@r-e , 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
)




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






View solution in original post

4 REPLIES 4
bhanu_gautam
Super User
Super User

@r-e , 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
)




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Super! ! !

 

let me try 

Hi @r-e ,

 

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
Super User
Super User

Hello @r-e , 

 

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 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.