Count patients with both diagnosis for Venn Diagram

[anwilkins]

I'm trying to create a Venn Diagram to show a count of active patients who have been diagnosed with issues related to

1-Mental Health

2-Substance Use

3-Both

I used the following code to create a new column categorizing each patient 

**Categories =

IF(LEFT('MERGE_ICD'[ICD9Code], 2) = "F1","Substance Use","" &

IF(LEFT('MERGE_ICD'[ICD9Code], 2) = "F2","Mental Health","" &

IF(LEFT('MERGE_ICD'[ICD9Code], 2) = "F3","Mental Health","" &

IF(LEFT('MERGE_ICD'[ICD9Code], 2) = "F4","Mental Health","" &

IF(LEFT('MERGE_ICD'[ICD9Code], 2) = "F5","Mental Health","" &

IF(LEFT('MERGE_ICD'[ICD9Code], 2) = "F6","Mental Health","" &

IF(LEFT('MERGE_ICD'[ICD9Code], 2) = "F7","Mental Health","" &

IF(LEFT('MERGE_ICD'[ICD9Code], 2) = "F8","Mental Health","" &

IF(LEFT('MERGE_ICD'[ICD9Code], 2) = "F9","Mental Health","N/A")))))))))  

I then created two more columns where I associated the number 1 if the patient was in that category and a 0 if no.

**Value-MH = IF('MERGE_ICD'[**Categories] IN {"Mental Health"} ,1,0)

**Value-SU = IF('MERGE_ICD'[**Categories] IN {"Substance Use"} ,1,0)

My problem is that my diagram is not showing the overlap of patients who have been diagnoses with both mental health and substance use codes

Adding the filter to remove the "0" in one removes the capability to look for the "1 in the second 

 I also have measures to calculate the data but still cant get the diagram to work..any help is greatly appreciated

 

*Total SU Patients = CALCULATE([*Total Patients], MERGE_ICD[**Categories] = "Substance Use")

*Total MH Patients = CALCULATE([*Total Patients], MERGE_ICD[**Categories] = "Mental Health")

*Total Patients = DISTINCTCOUNT((MERGE_ICD[patient internal id]))

Here is a sample visual of the data structure

Patient ID	Category	Service Date	MH Code	SU Code	Both
55433	Substance Use	1/1/2022	0	1	0	Total MH = 4
55433	Substance Use	1/2/2022	0	1		Total SU = 3
55433	Substance Use	1/3/2022	0	1		Total Both =2
88546	Mental Health	1/4/2022	1	1	1
88546	Mental Health	1/5/2022	1	1
88546	Mental Health	1/8/2022	1	1
88546	Mental Health	1/9/2022	0	1
88546	Mental Health	1/10/2022	0	1
88546	Mental Health	1/11/2022	1	1
88546	Mental Health	1/12/2022	1	1
25497	Mental Health	1/13/2022	1	0	1
25497	Mental Health	1/14/2022	1	0
25497	Mental Health	1/19/2022	1	0
25497	Mental Health	1/20/2022	1	0
25497	Mental Health	1/21/2022	1	0
25497	Substance Use	1/22/2022	1	1
56142	Mental Health	1/23/2022	1	0	0
56142	Mental Health	1/24/2022	1	0
56142	Mental Health	1/25/2022	1	0
56142	Substance Use	1/30/2022	1	0
56142	Substance Use	1/31/2022	1	0
66525	Substance Use	2/1/2022	1	0	0
66525	Substance Use	2/2/2022	1	0
66525	Substance Use	2/3/2022	1	0

[v-jingzhang · Accepted solution]

Hi @anwilkins 

You can create a new table with the following code

Table 2 = 
UNION (
    ROW ( "Mental Health", 1, "Substance Use", 0, "Number of Patients", CALCULATE(DISTINCTCOUNT('Table'[Patient ID]), FILTER('Table','Table'[MH Code]=1 && 'Table'[SU Code]=0) )),
    ROW ( "Mental Health", 0, "Substance Use", 1, "Number of Patients", CALCULATE(DISTINCTCOUNT('Table'[Patient ID]), FILTER('Table','Table'[MH Code]=0 && 'Table'[SU Code]=1) )),
    ROW ( "Mental Health", 1, "Substance Use", 1, "Number of Patients", CALCULATE(DISTINCTCOUNT('Table'[Patient ID]), FILTER('Table','Table'[MH Code]=1 && 'Table'[SU Code]=1) ))
)

Then use this new table to create the Venn Diagram. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[v-jingzhang · Accepted solution]

Hi @anwilkins 

Sorry I realized that there was a mistake in my previous solution which would count some overlapped patients more than once in the new table. Please try the new solution in this reply. This new solution doesn't have that problem. 

First create a table to summarize patients and categories that they have been diagnosed. 

Summarize Table = SUMMARIZE('Table','Table'[Patient ID],"MH Code",MAX('Table'[MH Code]),"SU Code",MAX('Table'[SU Code]))

Then use data from the above table to create the table for Venn Diagram. 

Table 2 = 
UNION (
    ROW ( "Mental Health", 1, "Substance Use", 0, "Number of Patients", COUNTROWS ( FILTER('Summarize Table','Summarize Table'[MH Code]=1 && 'Summarize Table'[SU Code]=0) )),
    ROW ( "Mental Health", 0, "Substance Use", 1, "Number of Patients", COUNTROWS ( FILTER('Summarize Table','Summarize Table'[MH Code]=0 && 'Summarize Table'[SU Code]=1) )),
    ROW ( "Mental Health", 1, "Substance Use", 1, "Number of Patients", COUNTROWS ( FILTER('Summarize Table','Summarize Table'[MH Code]=1 && 'Summarize Table'[SU Code]=1) ))
)

The new pbix has been attached. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[v-jingzhang · Accepted solution]

Hi @anwilkins 

You can create a new table with the following code

Table 2 = 
UNION (
    ROW ( "Mental Health", 1, "Substance Use", 0, "Number of Patients", CALCULATE(DISTINCTCOUNT('Table'[Patient ID]), FILTER('Table','Table'[MH Code]=1 && 'Table'[SU Code]=0) )),
    ROW ( "Mental Health", 0, "Substance Use", 1, "Number of Patients", CALCULATE(DISTINCTCOUNT('Table'[Patient ID]), FILTER('Table','Table'[MH Code]=0 && 'Table'[SU Code]=1) )),
    ROW ( "Mental Health", 1, "Substance Use", 1, "Number of Patients", CALCULATE(DISTINCTCOUNT('Table'[Patient ID]), FILTER('Table','Table'[MH Code]=1 && 'Table'[SU Code]=1) ))
)

Then use this new table to create the Venn Diagram. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[anwilkins]

Thanks! I have it working but still don't understand why I needed to create a new table vs just adding calculated columns to the existing table where the data resides. Thanks!