Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Pull data from multiple tables

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!
 
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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.