Showing results for 
Search instead for 
Did you mean: 
New Member

Needing Help with DAX Statement

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. 

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

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!

Not applicable

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.




Resident Rockstar
Resident Rockstar

Hi @SarQL,


could you see if this measure does the trick:

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


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



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

Helpful resources

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors