Forum Discussion

bigrods's avatar
bigrods
Helper III
5 years ago
Solved

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

 

Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    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
     

2 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    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's avatar
      bigrods
      Helper III
      5 years ago

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