Forum Discussion
Using ALLSELECTED and ALLEXCEPT Together to Get Max Date at Higher Level
My colleague was able to solve
Project Max Date (working) = CALCULATE(MAX(Sheet1[Date]),FILTER(AllSELECTED(Sheet1), Sheet1[Project #] = SELECTEDVALUE(Sheet1[Project #])))
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.
My colleague was able to solve