Count of rows with inclusion and exclusion criteria from 3 related tables?

[oscarsdad2024]

I am new to BI and working with healthcare data, so I am unable to post real data, Instead I have tried to outline the data model that I am working with. I am sturggling to write what I thought should be a simple query that looks for admissions meeting criteria on diagnosis codes. 

I have 3 tables:

Table A has the fields PatientID, AdmitDate, DischargeDate, PrimaryDiagnosisCode, DiagnosisComboID

Table B has the fields DiagnosisComboID, DiagnosisID

Table C has the fields DiagnosisID, DiagnosisCode

Basically table A has only the primary diagnosis code, table B is a bridge table between A and C, and Table C has the full list of diagnosis codes associated with the admit.

Table A is linked to Table B on Diagnosis Combo Key, Many to One, single cross-filter direction. Table B is linked to Table C on Diagnosis Key, Many to One, single cross-filter direction.

I want to write a DAX measure that gets a count of admissions from table A where the primary diagnosis code is in a specific list of codes (e.g. A, B, C) BUT none of the diagnosis codes from Table C are in the exclusion list X, Y, Z.

The query below works/gives no error, but it’s not returning the expected result – I know that some of the admits being counted do in fact have one of the exclusion codes associated with them.

Admits = CALCULATE(DISTINCTCOUNT(A[PatientID]), ReceivedClaimFact[PrimaryDiagnosisCode] IN {"A”, “B”, “C”}, Not C[DiagnosisCode] IN{"X”, “Y”, “Z”})

I have tried using ChatGPT to create a query, but the solutions it offered me resulted in errors...

I tried replying to the post below and it just wouldn't go through, so adding here....

Okay, I tried to create sample data to better illustrate what I'm trying to do.

First, there are 3 sample tables.

Admits

PatID	Admit Dat	Principal Diagnosis	DiagCombo
12345	1/1/2024	AAA	111
23456	1/2/2024	BBB	222
34567	1/3/2024	DDD	333
45678	1/4/2024	FFF	444
56789	1/5/2024	GGG	555
67890	1/6/2024	BBB	111

Bridge

Combo	DiagID
111	A
111	B
111	C
222	A
222	B
222	D
333	A
333	C
333	D
333	F
333	G
444	F
444	G
555	A
555	G
555	H
555	J

Diag

DiagID	DiagCode
A	AAA
B	BBB
C	CCC
D	DDD
F	FFF
G	GGG
H	HHH
J	JJJ

This is how I have them connected:

And here is the DAX query and the visualizations I'm using it on. The "correct" result / the result I'm looking for would be 2 - the patient ID 23456 should not be counted as 1 because it does have a DDD diag code associated with it. 

I assume there is something I'm leaving out of the query to cause this one to get counted still...

[vanessafvg]

how are you returning the value, in what visual?

if you are using a total type value from a visual ie card because you are doing a distinct count, you might not the correct values.

you will need to display how you are doing this.

[oscarsdad2024]

Okay, I tried to create sample data to better illustrate what I'm trying to do.

First, there are 3 sample tables.

Admits

PatID	Admit Dat	Principal Diagnosis	DiagCombo
12345	1/1/2024	AAA	111
23456	1/2/2024	BBB	222
34567	1/3/2024	DDD	333
45678	1/4/2024	FFF	444
56789	1/5/2024	GGG	555
67890	1/6/2024	BBB	111

Bridge

Combo	DiagID
111	A
111	B
111	C
222	A
222	B
222	D
333	A
333	C
333	D
333	F
333	G
444	F
444	G
555	A
555	G
555	H
555	J

Diag

DiagID	DiagCode
A	AAA
B	BBB
C	CCC
D	DDD
F	FFF
G	GGG
H	HHH
J	JJJ

This is how I have them connected:

And here is the DAX query and the visualizations I'm using it on. The "correct" result / the result I'm looking for would be 2 - the patient ID 23456 should not be counted as 1 because it does have a DDD diag code associated with it. 

I assume there is something I'm leaving out of the query to cause this one to get counted still...

[Anonymous]

Hi @oscarsdad2024 ,

You can create a measure as below to get it, please find the details in the attachment.

AdmitMeasure = 
VAR _digid =
    CALCULATE ( MAX ( 'Diag'[DiagID] ), 'Diag'[DiagCode] = "DDD" )
VAR _combos =
    CALCULATETABLE (
        VALUES ( 'Bridge'[Combo] ),
        FILTER ( 'Bridge', 'Bridge'[DiagID] = _digid )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Admits'[PatID] ),
        FILTER (
            'Admits',
            'Admits'[Principal Diagnosis]
                IN { "AAA", "BBB" }
                    && NOT ( 'Admits'[DiagCombo] IN _combos )
        )
    )

Best Regards

[oscarsdad2024]

Thank you for this response! This does in fact work for this example. However, what if I had more than one code I wanted to include in the exclusion bucket? I tried changing the = "DDD" to IN {"CCC", "DDD"} and it returns 2 still instead of 0. 

[Anonymous · Accepted solution]

Hi @oscarsdad2024 ,

Please update the formula of measure [AdmitMeasure] as below and check if it can return the expected result.

AdmitMeasure = 
VAR _digids =
    CALCULATETABLE (
        VALUES ( 'Diag'[DiagID] ),
        'Diag'[DiagCode] IN { "CCC", "DDD" }
    )
VAR _combos =
    CALCULATETABLE (
        VALUES ( 'Bridge'[Combo] ),
        FILTER ( 'Bridge', 'Bridge'[DiagID] IN _digids )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Admits'[PatID] ),
        FILTER (
            'Admits',
            'Admits'[Principal Diagnosis]
                IN { "AAA", "BBB" }
                    && NOT ( 'Admits'[DiagCombo] IN _combos )
        )
    )

Best Regards

[oscarsdad2024]

@Anonymous Thank you so much! This worked perfectly.