cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anwilkins
Helper I
Helper I

Pull medical codes from two (2) tables

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:

anwilkins_0-1635278247182.png

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

anwilkins_1-1635278390682.png

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

 

 

 

1 ACCEPTED SOLUTION
TheoC
Memorable Member
Memorable Member

@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.

TheoC_1-1635392208813.png

 

That will hopefully give you what you are after 🙂

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

View solution in original post

3 REPLIES 3
TheoC
Memorable Member
Memorable Member

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 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

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. 

anwilkins_0-1635333342617.png

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
Memorable Member
Memorable Member

@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.

TheoC_1-1635392208813.png

 

That will hopefully give you what you are after 🙂

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.