Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AgeOfEgos
Helper I
Helper I

Conditional column based on different rows (same Person_ID)

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!

2 REPLIES 2
AkhilAshok
Solution Sage
Solution Sage

Why don't you create a measure for Medication # = DISTINCTCOUNT(Table[Medication]) and use this as a Visual Level filter greater than 2? If your visual has other dimensions which breaks the group by, you could ignore those as well:

Medication # Name Level =
CALCULATE (
    DISTINCTCOUNT ( Table[Medication] ),
    ALLEXCEPT ( Table, Table[Name] )
)

 

If they have two opiods, wouldn't that still appear?  IE 

 

Patient A               Lortab 1

Patient A               Lortab 2

 

MedicationCount > 2 would be satisfied?  I"m needing the specific combination of a benzo + opioid

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors