How to check if ID value exists in another Type value column when filtered? (pbix file provided)

[db400]

Having trouble creating formula for the Desired_Column. Logic is that "Yes" will show for a Patient_ID with Visit_Type that is not "New Consult" if there is another identical Patient_ID with Visit_Type of "New Consult".

Date	Patient_ID	Visit_Type	Desired_Column
1/3/23	187	New Consult
1/5/23	039	Scan	No
1/7/23	188	New Consult
1/10/23	187	Lab	Yes
1/16/23	039	Surgery	No
1/20/23	189	New Consult
1/23/23	188	Scan	Yes
1/25/23	102	Visit	No
1/25/23	190	New Consult
1/28/23	190	Lab	Yes
1/29/23	056	Scan	No
1/30/23	187	Surgery	Yes

Logic examples:

187's Lab visit on 1/10/23 and Surgery visit on 1/30/23 shows "Yes" because they had a "New Consult" visit on 1/3/23.

039's Scan visit on 1/5/23 shows "No" because they have no "New Consult" visit, only a Surgery visit on 1/16/23.

188's Scan visit on 1/23/23 shows "Yes" because they had a "New Consult" visit on 1/7/23.

Link to .pbix file: 

https://drive.google.com/file/d/1BvQ8OwRuMjXKTMLHHuTCX7aVcGaxq7J6/view?usp=sharing

Thank you in advance!

[parry2k]

@db400 one more request, when you reply to the post, make sure to tag the person you are working with. 

BTW, you ran into the same issue, the data type of the date column in your file is text, change it to date and it will work.

[db400]

@parry2k 

Works beautifully. Thanks again for your help and also for the post feedback!

[parry2k]

@db400 and the link you shared to download the file is not working.

I think if you share sample data and clearly outline the logic, that is sufficient, sharing pbix file surely helps.

[parry2k]

@db400 working at my end, I just pasted the data from your post and worked on it, not downloading the pbix file, Maybe there is something different in the file like the previous one:

[db400]

That's strange.. I have tried re-doing the column adding but I am still getting the same problem.

The correct .pbix file link: 

https://drive.google.com/file/d/1BvQ8OwRuMjXKTMLHHuTCX7aVcGaxq7J6/view?usp=drive_link

[parry2k · Accepted solution]

@db400 thanks for making this a seperate post, otherwise a lot of people keep on asking the question on the existing post even though it is a new requirement. Kudos to you for following the etiquette to post a question. Thank you!

Well, this is what I think you are looking for:

Column 2 = 
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]

Thanks again for your help! I'll be posting quite a few help requests in the future so please let me know if there's anything else I should include in posts to make it easier for those helping.

It seems like the provided formula is not returning the desired result, please see highlighted below.