How to check if duplicate value exists in column after filter of another column?

[db400]

The intention is to create a new column (Desired_Column) indicating whether a patient (Patient_ID) that completed a New Consult continued onto a Procedure.

Date	Patient_ID	Visit_Type	Desired_Column
1/3/23	187	New Consult	Yes
1/5/23	039	Procedure
1/7/23	188	New Consult	Yes
1/10/23	187	Procedure
1/16/23	098	Procedure
1/20/23	189	New Consult	No
1/23/23	188	Procedure
1/25/23	102	Procedure
1/25/23	190	New Consult	No

I am rather new to using Power BI and am struggling on formulating a way to indicate whether Patient_ID still exists after Visit_Type is filtered to show "Procedure".

Thanks in advance.

[parry2k · Accepted solution]

@db400 here it is , small tweak, when I tested at my end the date data type was text and that was the issue:

Column = 
VAR __Patient = Patient[Patient_ID]
VAR __Date = Patient[Date]
VAR __Table =
FILTER ( ALLSELECTED ( Patient ), [Patient_ID] = __Patient && Patient[Date] >= __Date && Patient[Visit_Type] <>  "NPC"  )
RETURN
IF ( Patient[Visit_Type] =  "NPC" , IF ( NOT ISEMPTY ( __Table ), "Yes", "No" ) ) 

[db400]

It works flawlessly. Thank you so much!!

[parry2k]

@db400 I have no clue, you need to share the pbix file

[db400]

Here is the link to the .pbix file: 

https://drive.google.com/file/d/15AQVNauOH9BxvKMGkk7QKjmEhy7Jo909/view?usp=sharing

[parry2k]

@db400 not sure why, can you make sure there are invisible spaces with "New Consult" values?

[db400]

I adjusted the table and formula to show "NPC" instead of "New Consult" instead to avoid any issues with invisible spacing and it still doesn't fix the issue. 

[parry2k]

@db400 try using this DAX to add a calculated column:

Column = 
VAR __Patient = Patient[Patient_ID]
VAR __Date = Patient[Date]
VAR __Table =
FILTER ( ALLSELECTED ( Patient ), [Patient_ID] = __Patient && Patient[Date] <= __Date && Patient[Visit_Type] <> "New Consult" )
RETURN
IF ( Patient[Visit_Type] = "New Consult", IF ( NOT ISEMPTY ( __Table ), "Yes", "No" ) ) 

[db400]

Thank you, but that didn't seem to give the desired results. Please see highlighted below.

[db400]

189 and 190 are no because they do not show up again in the table with a "Procedure" Visit_Type like 187 and 188 do.

[parry2k]

@db400 why 189 and 190 are no? What's the logic?