Choose a specific value based on a group of dates

[Synik_PBI]

Hello all, I'm a bit stuck on what I thought was a simple problem, hoping for some advice on setting this up. 

I'd like to filter a table visual down based on some specific criteria. Based on the max date and an ID, a user should be able to choose a selection to slice a table down. Since multiple choices need to be available, I started using a SWITCH statement. Here is some sample data for example: 

In the top example, this would NOT be included at all because the most recent date is cancelled. 

Conversely, in the lower example, the most recent date is something the customer wants to see, so I would need to specify if the status = "Pending TASK" then to display "Pending TASK" as an option in the slicer. I have about 5 different types of statuses to account for, hence my thinking to start off with a SWITCH. 

Expected output: I should have a slicer with about 5 different options for the user to choose from. For example, if one of the options is "Pending TASK", if a user chooses that, it should display all data where the status "Pending TASK" is the max date. The difficulty here lies in that I need to taker both Date and ID into consideration when looking at the status. 

Any thoughts on this? There has to be something simple I'm missing! Thank you! 

EDIT: some clarification on the 2nd picture. There is about 5 different status I'm looking to show of around 20 total. I would have cases like this: 

where I don't care about this status. So even if it's the max date, the status is not one I care about so it wouldn't show up as a selection in the SWITCH statement. 

EDIT 2: 
@lbendlin - I have uploaded a .xlsx file with example data to Google drive. https://docs.google.com/spreadsheets/d/1fqu3EHwQK7jLFp_TxQfiqcto19bUUOxA/edit?usp=drive_link&ouid=11...

[lbendlin]

Right click on the values and choose "Include"  or "Exclude".

[Synik_PBI]

I'm sorry, can you elaborate? I'm confused by this. 

[lbendlin]

[Synik_PBI]

I don't believe this is exactly what I'm looking for. I need this logic automated to say that if the date is max and cancelled, don't show. Otherwise, I would manually need to do this for 10,000 rows currently, plus daily for any additional rows. 


This also does nothing for cases in the 2nd example where a specific status would need to show up. 

Apologies if I explained this poorly. 

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Synik_PBI]

I have added an example file via .xlsx and have tried to provide clarity with an expected output. 

[lbendlin]

I don't see the expected output.  Looks like you have lots of overlapping statuses. 

So you want to find if a particular status shares the latest start date for an ID ?

[Synik_PBI]

Not exactly. The ID is the main source of the grouping, so the status would first need to take into account the same ID, then look at the most recent start date. 

Here is some examples using the example data sheet: 

ID 4152 has a most recent start date of completed. I don't care about this one because its not pending anying. Same thing with canceled. If 4152 had the most recent date of 10/29/2021, then I would care because the most recent would be pending. 

ID 8606 is one I want to show up as an option in the slicer for "Pending IT". Since this ID has a max date of a pending status, this should be a selected value in the slicer for "Pending IT". 

Both of these examples are the same I was trying to illustrate above with the screenshots in my OP. Hope this helps. 

[lbendlin]

ID 8606 is one I want to show up as an option in the slicer for "Pending IT". Since this ID has a max date of a pending status, this should be a selected value in the slicer for "Pending IT". 

why Pending IT and not Submitted?  Are you only interested in the ones that are pending something?

[Synik_PBI]

Yes, that is the ask here. If I cared about only the max date's value, this wouldn't be confusing me so much.

The user only cares about things pending and the way the dataset is created is using a SQL Query where requires a user to enter an ID. I have expanded on the original intention by displaying all the IDs at once, but the problem is the user doesn't know which is the most recent date related to an ID. 

If a user slices on an ID, they get the answer, as they can see the max date status. Unfortunately with over 10,000 IDs, they would need to go one-by-one. 

If a user slices on a Pending status, they see all the IDs that were ever in pending, but the problem here is it may not be the max date. They could be looking at something pending a year ago, but has long since been completed/canceled. 

Hope that helps. 

[lbendlin · Accepted solution]

This should do it

see attached

[Synik_PBI]

Thank you @lbendlin ! I was able to translate you're logic over to the live .pbix and its working as expected. While not the solution I would have went with, as it seems to have some problems with other visuals on the page, this is working correctly. 

Thank you for working this out! I would have never thought of this setup!