Forum Discussion

TSK's avatar
TSK
Frequent Visitor
6 years ago
Solved

DAX for Measure

I need help in creating measure, I have 3 tables 1) Patient Name 2) Patient Diagnosis 3) Medicene Consumed. I have linked diagnosis and medicene table with Patient Name table via ID. Now I want a measure which can calculate that how many patients are diagnosed as "Fever" and consumed "Panadol" and "Arinac". Below are the tables for reference in this case answer is 2, Patient with ID 1 and 5 are Diagnosed as Fever and Consumed "Panadol" and "Arinac". How can I achieve this via measure.

Patient Name:

IDName
1Ahmed
2Rafiq
3Saleem
4Jospeh
5David

 

Patient Diagnosis:

IDDiagnosis
1Fever
2Flu
3Flu
4Flu
5Fever

 

Medicene Consumed:

IDMedicine Name
1Panadol
1Brufen
1Arinac
2Panadol
2Arinac
3Panadol
4Panadol
5Panadol
5Arinac
Need Help
  • TomMartens's avatar
    TomMartens
    6 years ago

    Hey TSK ,

     

    this DAX measure allows counting the patients, who are consuming the selected medicine:

    complex count = 
var patients = 
    CALCULATETABLE(
        VALUES('Patient Name'[ID])
        , 'Patient Diagnosis'
        , 'Medicine Consumed'
    )
var medicineconsumed = VALUES('Medicine Consumed'[Medicine Name])
return
COUNTROWS(
    FILTER(
        patients
        , countrows(
            INTERSECT(
                CALCULATETABLE(
                    VALUES('Medicine Consumed'[Medicine Name])
                )
                , medicineconsumed
            ))
            = COUNTROWS(medicineconsumed)
        )
)

    This allows to count the patients who are consuming at least both of selected medicines, please be aware that the screenshot show the number of patients diagnosed with the flu:

    If you are looking for a measure that counts the patients who are consuming exactly the selected medicines, things become a little more complex. In your example, patient 1 also consumes the medicament Brufen, but is counted as this patient also takes both of the selected medicines.

     

    Hopefully, this helps to tackle your challenge.

     

    Regards,

    Tom

     

1 Reply

  • TomMartens's avatar
    TomMartens
    Super User
    6 years ago

    Hey TSK ,

     

    this DAX measure allows counting the patients, who are consuming the selected medicine:

    complex count = 
var patients = 
    CALCULATETABLE(
        VALUES('Patient Name'[ID])
        , 'Patient Diagnosis'
        , 'Medicine Consumed'
    )
var medicineconsumed = VALUES('Medicine Consumed'[Medicine Name])
return
COUNTROWS(
    FILTER(
        patients
        , countrows(
            INTERSECT(
                CALCULATETABLE(
                    VALUES('Medicine Consumed'[Medicine Name])
                )
                , medicineconsumed
            ))
            = COUNTROWS(medicineconsumed)
        )
)

    This allows to count the patients who are consuming at least both of selected medicines, please be aware that the screenshot show the number of patients diagnosed with the flu:

    If you are looking for a measure that counts the patients who are consuming exactly the selected medicines, things become a little more complex. In your example, patient 1 also consumes the medicament Brufen, but is counted as this patient also takes both of the selected medicines.

     

    Hopefully, this helps to tackle your challenge.

     

    Regards,

    Tom