distinct count by multiple columns

[bigrods]

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 ID	Event staff type	Drug_Name	Month_Number	Month
30408763	General Medical Practitioner	Amitriptyline	4	April
16092666	Nurse Access Role	Mirtazapine	8	August
16092666	Nurse Access Role	Codeine	8	August
16092666	Nurse Access Role	Diazepam	8	August
52211931	Nurse Access Role	Amitriptyline	10	October
52211931	General Medical Practitioner	Amitriptyline	1	January
52211931	Pharmacist	Amitriptyline	9	September
52211931	General Medical Practitioner	Amitriptyline	7	July
52211931	Technician - PS&T	Amitriptyline	8	August
52211931	General Medical Practitioner	Amitriptyline	10	October
52211931	Associate Practitioner	Amitriptyline	11	November
52211931	Nurse Access Role	Amitriptyline	12	December
52211931	General Medical Practitioner	Methadone	12	December
52211931	General Medical Practitioner	Amitriptyline	1	January
22242833	Nurse Access Role	Amitriptyline	2	February
22242833	General Medical Practitioner	Amitriptyline	3	March
51635024	General Medical Practitioner	Amitriptyline	5	May
51635024	Technician - PS&T	Nefopam	7	July
51635024	General Medical Practitioner	Methadone	8	August
51635024	General Medical Practitioner	Amitriptyline	6	June
51635024	Nurse Access Role	Amitriptyline	7	July
53880227	Nurse Access Role	Amitriptyline	1	January
4286424	Pharmacist	Amitriptyline	10	October
4286424	Technician - PS&T	Amitriptyline	11	November
4286424	Technician - PS&T	Oxycodone	12	December
4286424	Nurse Access Role	Amitriptyline	1	January
18610202	Nurse Access Role	Diazepam	5	May
18610202	Nurse Access Role	Diazepam	5	May
1237970	Nurse Access Role	Methadone	8	August
18610202	Nurse Access Role	Diazepam	4	April

[mahoneypat · Accepted solution]

Please try these expressions to get the result shown.

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

[bigrods]

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