Forum Discussion
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:
I want to return the Max Date at the Project level.
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.
8 Replies
- Jihwan_Kim
Super User
Hi,
Please try the below.
Project Max Date ALLSELECTED =CALCULATE(MAX(Sheet1[Date]),ALLSELECTED(Sheet1))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.
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.
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.
- tamerj1
Community Champion
Hi Anonymous
Try
CALCULATE (
MAXX ( ALLSELECTED ( Sheet1[Job #] ), Sheet1[Date] ),
ALLSELECTED ( Sheet1[Project #] )
)
- tamerj1
Use MAX ( Sheet1[Date] )
