Needing Help with DAX Statement

[Anonymous]

Background: I'm trying to create a denominator for Patients that have a Disease Diagnosis Date on or before the time they started taking the Drug. We want to make sure to only capture with high confidence that the patients are taking the drugs we are interested in AFTER they were diagnosed with the disease we are interested in. The thought is if someone is taking a drug before their diagnosis date then they most likely were taking that drug for a different disease.

Table/Relationship Structure: Patient dimension table in the center of the schema, fact Drug table (many drugs to one patient), fact Disease table (many diseases to one patient). There is no direct relationship between Drug and Disease tables.

Silly Example - yes this isn't a disease: 
Drugs of Interest: Advil & Tylonol

Patient 109247 took Advil on 1/8/2020
Patient 109247 took Tylonol on 1/17/2020
Patient 109247 was diagnosed with Headache on 1/20/2020
This patient should not be counted in our denominator at all because they were diagnosed after taking the drugs. 

SQL:
SELECT Count (Distinct dr.PatientID)
FROM DrugTable dr
INNER JOIN DiseaseTable di on di.PatientID = dr.PatientID
WHERE dr.DrugDate >= di.DiseaseDate

DAX:
CALCULATE(
DISTINCTCOUNT(DrugTable[PatientID]),
FILTER (DrugTable, DrugTable[DrugDate] >= DiseaseTable[DiseaseDate]))

*The Filter function is where I'm having difficulties because it can't establish a relationship between drug and disease tables. Help pls!

[Anonymous]

Here's a full solution with much more than you wanted. Please play around with the visuals to understand what they are showing and calculating.

Best

D

[sturlaws]

Hi @Anonymous,

could you see if this measure does the trick:

number of patients =
COUNTROWS (
    GROUPBY (
        FILTER (
            GENERATE (
                GENERATE (
                    VALUES ( Patient[PatientID] );
                    CALCULATETABLE ( VALUES ( Diagnose[diagnoseDate] ) )
                );
                CALCULATETABLE ( VALUES ( Drug[drugDate] ) )
            );
            Drug[drugDate] >= Diagnose[diagnoseDate]
        );
        Patient[PatientID]
    )
)

I have not tested it thoroughly, just used the patient you provided + one I invented myself.

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.