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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bigrods
Helper II
Helper II

distinct count by multiple columns

Hi everyone,

 

I have a dataset of Patient ID's and the Drugs they have been prescribed, and the Month prescribed.

 

I'd like to create (a measure?) that tells me:

 

How many patients were prescribed 2 or more drugs each month

How many were prescribed 2 or more excluding Methadone each month

How many were prescribed Methadone and 1 other each month

 

I've tried a few things like count distinct and summarizing the table but can't quite figure it out. Would be very greateful to anyone who can suggest anything!

 

My dataset looks like this:

 

Patient IDEvent staff typeDrug_NameMonth_NumberMonth
30408763General Medical PractitionerAmitriptyline4April
16092666Nurse Access RoleMirtazapine8August
16092666Nurse Access RoleCodeine8August
16092666Nurse Access RoleDiazepam8August
52211931Nurse Access RoleAmitriptyline10October
52211931General Medical PractitionerAmitriptyline1January
52211931PharmacistAmitriptyline9September
52211931General Medical PractitionerAmitriptyline7July
52211931Technician - PS&TAmitriptyline8August
52211931General Medical PractitionerAmitriptyline10October
52211931Associate PractitionerAmitriptyline11November
52211931Nurse Access RoleAmitriptyline12December
52211931General Medical PractitionerMethadone12December
52211931General Medical PractitionerAmitriptyline1January
22242833Nurse Access RoleAmitriptyline2February
22242833General Medical PractitionerAmitriptyline3March
51635024General Medical PractitionerAmitriptyline5May
51635024Technician - PS&TNefopam7July
51635024General Medical PractitionerMethadone8August
51635024General Medical PractitionerAmitriptyline6June
51635024Nurse Access RoleAmitriptyline7July
53880227Nurse Access RoleAmitriptyline1January
4286424PharmacistAmitriptyline10October
4286424Technician - PS&TAmitriptyline11November
4286424Technician - PS&TOxycodone12December
4286424Nurse Access RoleAmitriptyline1January
18610202Nurse Access RoleDiazepam5May
18610202Nurse Access RoleDiazepam5May
1237970Nurse Access RoleMethadone8August
18610202Nurse Access RoleDiazepam4April

 

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

Please try these expressions to get the result shown.

 

mahoneypat_0-1612878085812.png

Customers 2+ = COUNTROWS(FILTER(DISTINCT(Scripts[Patient ID]), CALCULATE(DISTINCTCOUNT(Scripts[Drug_Name]))>=2))
 
Customers 2+ No Methadone = var vCustomers = FILTER(DISTINCT(Scripts[Patient ID]), ISBLANK(CALCULATE(COUNTROWS(Scripts), Scripts[Drug_Name] = "Methadone")))
return COUNTROWS(FILTER(vCustomers, CALCULATE(DISTINCTCOUNT(Scripts[Drug_Name]))>=2))
 
Customers 1 and Methadone = var vCustomers = FILTER(DISTINCT(Scripts[Patient ID]), NOT(ISBLANK(CALCULATE(COUNTROWS(Scripts), Scripts[Drug_Name] = "Methadone"))))
return COUNTROWS(FILTER(vCustomers, CALCULATE(DISTINCTCOUNT(Scripts[Drug_Name]))=2))
 
Pat
 




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

2 REPLIES 2
mahoneypat
Employee
Employee

Please try these expressions to get the result shown.

 

mahoneypat_0-1612878085812.png

Customers 2+ = COUNTROWS(FILTER(DISTINCT(Scripts[Patient ID]), CALCULATE(DISTINCTCOUNT(Scripts[Drug_Name]))>=2))
 
Customers 2+ No Methadone = var vCustomers = FILTER(DISTINCT(Scripts[Patient ID]), ISBLANK(CALCULATE(COUNTROWS(Scripts), Scripts[Drug_Name] = "Methadone")))
return COUNTROWS(FILTER(vCustomers, CALCULATE(DISTINCTCOUNT(Scripts[Drug_Name]))>=2))
 
Customers 1 and Methadone = var vCustomers = FILTER(DISTINCT(Scripts[Patient ID]), NOT(ISBLANK(CALCULATE(COUNTROWS(Scripts), Scripts[Drug_Name] = "Methadone"))))
return COUNTROWS(FILTER(vCustomers, CALCULATE(DISTINCTCOUNT(Scripts[Drug_Name]))=2))
 
Pat
 




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Wow Pat thank you so much! This has worked a treat 🙂 I don't think I would have ever got that!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.