Pull medical codes from two (2) tables

[anwilkins]

I want to assess the number of patients who come in for a mental health / substance use evaluation and have to wait more than 21 days till the treatment plan appaointment and first therapy sessions are scheduled. 

Table 1 holds the Column[Document Summary] where I seek the following codes:

Treatment Plan

Pie Note (Session)

Discharge

This table is driven by the Document Date 

Table 2 holds the column [CPT Code] where I only need the code:

90791 (it equates to the initial CCA / Assessment)

This table is driven by the Service Date

Ideally I'd like a grid that displays as follows:

My tables are linked on the common patient id and the structure looks like this:

I can add a column to a new custom table, add a column to an existing table, or write a measure, I just need to know which is the best path. 

Thanks in advance for the assistance

[TheoC]

Hi @anwilkins 

Ultimately, the "best" path is very dependent upon your current and future needs. For example, do you intend on using the outputs to manipulate down the track?  If so, I strongly recommend you consider Grouping / Merging the two tables using Power Query.

Otherwise, as you've mentioned, you can certainly create a new custom table and add the columns you require, and establish that as the basis of your output table. This option is a very good option as well. 

If you want to minimise the amount of tables, columns, etc., then I would go with your approach of a Calculated Column(s) on the existing table to return what you require.

My preference, and purely from the perspective of minimising the amount of DAX / coding, etc., I am using, would be to prepare the outputs in Power Query so that there's less "clutter" in the Report / Canvas view.  In saying that, it is a personal preference and others may have different views / thoughts.

Hope this has helped (and not added to confusion) but I think whatever path you choose, you're on a very good track given that you have already presented several great options in your post! 🙂

Best of luck with it all.

Theo 

[anwilkins]

Thanks Theo but I guess I wasn't clear...I need help writing the script more than confirmation of the idea. I need to know the process (whether it's creating a calculated column, or writing DAX) to get this info...

Look in MERGE_BillingPayment table, CPT Code column, and return all records where CPT Code equals 90791 AND look in MERGE_Documents table, Document Summary column, and pull all records where Document Summary equals Treatment Plan, Pie Note and Discharge.

I want those 4 events to serve as column headers in a Matrix. Once the Service and Document Dates are added, I hope to use Conditional Formatting to highlight those spans greater than 21 days between events. Here is a pic of the original effort where all the data was pulled from the Documents table, however, I know it is not working correctly as I have blank dates and empty fields. 

So the short question is, step-by-step, how to pull multiple codes, from two columns in two different tables?

Hope that helps clarify a bit. 

[TheoC · Accepted solution]

@anwilkinsof course, that makes sense. 

1. In the MERGE_Documents table, create a Calculated Column and enter the following:

col_CPTCode = IF ( LOOKUPVALUE ( MERGE_BillingPayment[CPT Code] , MERGE_BillingPayment[CPT Code] , "90791" ) = "90791" , "90791" , "" )

To get the table you want, just create a Table with all of the fields you want from the MERGE_Documents table and once you done that, all you need to do is drag the new col_CPTCode Calculated Column into the Filter pane of the Table visual and untick "Blanks" so that only 90791 is selected.

That will hopefully give you what you are after 🙂