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 pres...
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
     
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!