cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SiroPW
Frequent Visitor

Max & Min Dates Only Filter (top1/bottom1)

HI,

 

I have a dataset like follows:

 

DateValue
2019/08/0110
2019/08/0120
2019/08/0130
2019/08/0140
2019/08/0150
2019/07/01100
2019/07/01200
2019/07/01300
2019/06/011
2019/06/012
2019/06/013
2019/06/014
2019/05/011000
2019/05/012000

 

What I would like to do is be able to filter on the max and minimum dates so the result would be:

DateValue
2019/08/0110
2019/08/0120
2019/08/0130
2019/08/0140
2019/08/0150
2019/05/011000
2019/05/012000

 

I could achieve the same effect by manually ticking the 'Date' basic filter but I would have to change it every time the dataset updates. Similarly I could do topN filters for top and bottom dates but can't apply both at the same time AFAIK.

 

Any help is appeciated, thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Max & Min Dates Only Filter (top1/bottom1)

Hi @SiroPW ,

 

The formula in your measure is missing another condition:

Return IF(MAX('Table'[Date])=maxd||MAX('Table'[Date])=mind,"Yes","No")

I attached my pbix here for your reference, also please share yours if you are still stuck in it. 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

6 REPLIES 6
Super User I
Super User I

Re: Max & Min Dates Only Filter (top1/bottom1)

i would use a calculated colunm to check if the date is equal to the max or min date and then filter on this colunm 

 

example colunm below

 

MAX&MIN =
-- first get the max date as a veriable
var Maxd = max('Table'[Date])
-- next get the min date as a veriable
var mind = min('Table'[Date])
-- get the date in the row
var d= 'Table'[Date]
-- check if the date is equal to eaither of the two dates and if it is return TRUE if not RETURN FALSE
return if(or(d = Maxd,d =mind),TRUE, FALSE)
 
 
this colunm can then be used as a filter in the table to display only those that are true
Untitled.png




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




SiroPW
Frequent Visitor

Re: Max & Min Dates Only Filter (top1/bottom1)

Thanks for the reply.

 

Do you know how I could modify this to work with only the filtered/selected values?

 

I tried ALLSELECTED like below but I'm getting an error "The MAX function only accepts a column reference as an argument".

 

MAX&MIN =

-- first get the max date as a veriable

var Maxd = max(ALLSELECTED('Table'[Date]))
-- next get the min date as a veriable
var mind = min(ALLSELECTED('Table'[Date]))
-- get the date in the row
var d= 'Table'[Date]
-- check if the date is equal to eaither of the two dates and if it is return TRUE if not RETURN FALSE
return if(or(d = Maxd,d =mind),TRUE, FALSE)
Community Support
Community Support

Re: Max & Min Dates Only Filter (top1/bottom1)

Hi @SiroPW ,

 

You can take try of this one:

MAX&MIN = var maxd = MAXX(ALLSELECTED('Table'),[Date])
var mind = MINX(ALLSELECTED('Table'),[Date])
Return IF(MAX('Table'[Date])=maxd||MAX('Table'[Date])=mind,1,0)

7.PNG

When the date were filtered, it still works:

8.PNG

 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
SiroPW
Frequent Visitor

Re: Max & Min Dates Only Filter (top1/bottom1)

Thanks, I've tried a lot of combinations/tweaks of your answer but I still can't quite get it to filter properly.

 

It basically ends up returning True/1 for every record.

 

My latest attempt was to strip it back:

 

MAX&MIN =
var maxd = MAXX(ALLSELECTED(portfolio_eom),[as_at_date])
var mind = MINX(ALLSELECTED(portfolio_eom),[as_at_date])
Return IF(MAX(portfolio_eom[as_at_date])=maxd,"Yes","No")
 
I can see the maxd is returning the correct date and MAX(portfolio_valuation_eom[as_at_date]) by itself is too (they match), however every record returns "Yes".
 
Any ideas why? I feel like it should be something simple I'm missing. Thanks
SiroPW
Frequent Visitor

Re: Max & Min Dates Only Filter (top1/bottom1)

I think the problem is the ALLSELECTED is not working my my Date slicer.

Community Support
Community Support

Re: Max & Min Dates Only Filter (top1/bottom1)

Hi @SiroPW ,

 

The formula in your measure is missing another condition:

Return IF(MAX('Table'[Date])=maxd||MAX('Table'[Date])=mind,"Yes","No")

I attached my pbix here for your reference, also please share yours if you are still stuck in it. 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors