Conditional Column based on the presence of two other columns having a value (grouped by patient ID)

[AgeOfEgos]

I want a list of patients that take 2 (Or more) medication types at the same time.  The table would be;

Name                     Benzos____________Opioids___________Medication

Patient A                 Y                               N                         Xanax

Patient B                 Y                               N                         Xanax

Patient C                 Y                               N                         Xanax

Patient C                 N                              Y                         Lortab

Patient D                N                               Y                          Lortab

Patient E                Y                                N                         Xanax

Patient E                N                               Y                          Hydrocodone

Ideally, my visual would only display Patient C and Patient E (Those patients that had both a Benzo and Opioid medication, although different rows).  I can't group by--as that will break the rest of my report.  If I try a new Conditional Column (Or custom column with an IF statement), since it evaluates row by row without grouping by Patient--I end up with the same issue.  Thanks for any help!

[PattemManohar · Accepted solution]

@AgeOfEgos Please try this using "New Column" option

MultiMedicationFlag = 
IF(AND(PatMedication[Benzos]="Y",PatMedication[Opioids]="Y"),"Y",
 IF(AND(PatMedication[Benzos]="Y",COUNTROWS(FILTER(ALL(PatMedication),PatMedication[Opioids]="Y" && PatMedication[Name] = EARLIER(PatMedication[Name])))>0),"Y",
 IF(AND(PatMedication[Opioids]="Y",COUNTROWS(FILTER(ALL(PatMedication),PatMedication[Benzos]="Y" && PatMedication[Name] = EARLIER(PatMedication[Name])))>0),"Y","N")
 )
 )

[AgeOfEgos]

That was brilliant, thank you!