Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
anwilkins
Resolver II
Resolver II

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

@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
Super User
Super User

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. 

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.