cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Using ALLSELECTED and ALLEXCEPT Together to Get Max Date at Higher Level

Hi all,

 

This seems like a pretty basic challenge I have. And it's driving me nuts that I can't figure it out.

 

Sample dataset:

       kewaynes33_0-1646103585873.png

 

I want to return the Max Date at the Project level. 

 

Project Max Date = CALCULATE(MAX(Sheet1[Date]),
                                       ALLEXCEPT(Sheet1,Sheet1[Project #]))
 
Works like a charm:
kewaynes33_2-1646103653780.png

 

However, I want it to take the filtered selection into consideration. i.e. - When I'm filtered to just Job 1, it will reflect a Max date of 1/1/22. I figured I could just do something like this and wrap an allselected around my first measure, but no dice.

 
Project Max Date ALLSELECTED = CALCULATE([Project Max Date],
                                                                           ALLSELECTED(Sheet1))
kewaynes33_3-1646103806422.png

 

 Any help is grealy appreciated!

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

My colleague was able to solve

 

Project Max Date (working) = CALCULATE(MAX(Sheet1[Date]),

FILTER(AllSELECTED(Sheet1), Sheet1[Project #] = SELECTEDVALUE(Sheet1[Project #])))

View solution in original post

8 REPLIES 8
tamerj1
Super User
Super User

Hi @Anonymous 

Try

CALCULATE (

MAXX ( ALLSELECTED ( Sheet1[Job #] ), Sheet1[Date] ),

ALLSELECTED ( Sheet1[Project #] )

)

Anonymous
Not applicable

Thanks. But it gives me an error when I try to use Sheet1[Date]

kewaynes33_0-1646164272994.png

 

Use MAX ( Sheet1[Date] )

Also this might work
MAXX ( ALLSELECTED ( Sheet1[Job #] ), CALCULATE ( VALUES ( Sheet1[Date] ) ) ),

ALLSELECTED ( Sheet1[Project #] )

)

Anonymous
Not applicable

I haven't tried your last one. But wanted to message that my colleuge was able to solve.

 

Project Max Date (working) = CALCULATE(MAX(Sheet1[Date]),

FILTER(AllSELECTED(Sheet1), Sheet1[Project #] = SELECTEDVALUE(Sheet1[Project #])))
Jihwan_Kim
Super User
Super User

Hi,

Please try the below.

 

Project Max Date ALLSELECTED =
CALCULATE(MAX(Sheet1[Date]),
ALLSELECTED(Sheet1)
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Anonymous
Not applicable

Thanks for the quick repsonse, JiHwan. Yes, you are correct. That did work. And sorry, I should have included another job to throw another wrinkle into this.

 

I've added another job to the dataset.

kewaynes33_0-1646155289555.png

 

 

What I would like to see is the Max date for each Project based on the filtered selection.

So my original formula works as long as everything is selected.

kewaynes33_1-1646155289654.png

 

However, when I filter down to just the Jobs title '1', it doesn't. The Project 1/Job 1 is still reflecting the Max date of the Project 1/Job 2, even though it's now filtered out.

And using the second forumula will give the Max date of the entire filtered selection. However, I still want it to consider the filter context based on the Project.

 

Project 1/Job 1 should reflect it's own date (now that Project 1/Job 2 is filtered out). So neither of these formulas get that right. I figured it would need to be a combination of the formulas. But I'm having trouble nesting the ALL and/or CALCULATES in order to get it to work.

kewaynes33_2-1646155289888.png

 

 

Anonymous
Not applicable

My colleague was able to solve

 

Project Max Date (working) = CALCULATE(MAX(Sheet1[Date]),

FILTER(AllSELECTED(Sheet1), Sheet1[Project #] = SELECTEDVALUE(Sheet1[Project #])))

Helpful resources

Announcements
Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors