Pull data from multiple tables

[anwilkins]

Requesting help pulling data from two different tables.

Table 1 (MERGE_BillingSUMMARY) holds CPT codes.

Table 2 (MERGE_Observations) holds ICD9 codes.

I created the following 4 columns (2 in each table) and now need a 5th column or a measure that shows which patients are compliant and not compliant. To be compliant, a patient has to have been given a blood test (indicated by a cpt code) AND have been given a mental health test (indicated by ICD codes).

In MERGE_BillingSUMMARY I created...

*Blood Test =

IF('MERGE_BillingSUMMARY'[CPT Code] IN {"80047", "80048", "80050", "80053", "80069", "82947", "82950", "82951"}, "Glucose",

IF('MERGE_BillingSUMMARY'[CPT Code] IN {"83036", "83037"}, "HgbA1c",

IF('MERGE_BillingSUMMARY'[CPT Code] IN {"3044F", "3046F"}, "CPT-CAT II", "N/A")))

*Blood Test Met =

IF(MERGE_BillingSUMMARY[*Blood Test] IN {"Glucose", "HgbA1c", "CPT-CAT II"}, "Tested", "Not Tested" )

In MERGE_Observationss I created...

 *MH Test =

IF('MERGE_Observations'[ICD9Code] IN {"F20.0", "F20.2", "F20.3", "F20.4", "F20.5", "F20.8", "F20.89", "F20.9", "F25.0", "F25.1", "F25.8", "F25.9"}, "Schizophrenia",

IF('MERGE_Observations'[ICD9Code] IN {"F30.3", "F30.4", "F30.8", "F30.9","F31.10","F31.11","F31.12","F31.13", "F31.2", "F31.30", "F31.31", "F31.32", "F31.4","F31.5", "F31.60", "F31.61", "F31.62", "F31.63", "F31.64","F31.70", "F31.71", "F31.72", "F31.73", "F31.74", "F31.75", "F31.76", "F31.78"}, "Bipolar", "N/A"))

*MH Met =

IF('MERGE_Observations'[*MH Test] IN {"Bipolar", "Schizophrenia"}, "Tested", "Not Tested")

I need the dax (new column or measure) that says, shows patients with a "1" in both Test Met columns. For Example: (This dax is not working

Compliant Patient =

IF(VALUE(MERGE_BillingSUMMARY[*Blood Test Met])=1 && VALUE(MERGE_Observations[*MH Met])=1, "Compliant",

IF(VALUE(MERGE_BillingSUMMARY[*Blood Test Met])=0 && VALUE(MERGE_Observations[*MH Met])=1, "Not Compliant",

IF(VALUE(MERGE_BillingSUMMARY[*Blood Test Met])=1 && VALUE(MERGE_Observations[*MH Met])=0, "Not Compliant",

IF(VALUE(MERGE_BillingSUMMARY[*Blood Test Met])=0 && VALUE(MERGE_Observations[*MH Met])=0, "Not Compliant"))))

The end result will be a tabular chart like this but only showing Facility, Clinician, Patient ID, Compliant or Not Compliant. In the new column on the chart, the items in red would show as Compliant while others would show not compliant

Thanks!

[v-eqin-msft]

Hi @anwilkins ,

If there is a relationship between MERGE_BillingSUMMARY table and MERGE_Observations table? Actually we need more details about your tables to help us clarify your scenario. You could create a dummy sample for us to test or  

share your pbix file after removing sensitive data.

Refer to:

How to Get Your Question Answered Quickly - Microsoft Power BI Community

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

In addition, Your [Compliant Patient] may be simplified:

Compliant Patient =
IF(MERGE_BillingSUMMARY[*Blood Test Met]=1 && MERGE_Observations[*MH Met]=1, "Compliant","Not Compliant")

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[anwilkins · Accepted solution]

Hi Eyelyn, the shortened DAX did help but I ended up merging the two tables into a new table to resolve my issue with pulling data from two tables. Thanks for responding.