cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SarQL
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. 

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!

2 REPLIES 2
Anonymous
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.

 

Best

D

sturlaws
Resident Rockstar
Resident Rockstar

Hi @SarQL,

 

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.