DAX for Measure

[TSK]

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:

ID	Name
1	Ahmed
2	Rafiq
3	Saleem
4	Jospeh
5	David

Patient Diagnosis:

ID	Diagnosis
1	Fever
2	Flu
3	Flu
4	Flu
5	Fever

Medicene Consumed:

ID	Medicine Name
1	Panadol
1	Brufen
1	Arinac
2	Panadol
2	Arinac
3	Panadol
4	Panadol
5	Panadol
5	Arinac

[TomMartens · Accepted solution]

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