Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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 🙂
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
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.
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.
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
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |