Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Working with medical data from two data sources. We offer both drug and alcohol services as well as other counseling, physical health services, etc... I am seeking the average number of days a patient remained in other programs after being discharged from say a substance use program.
I pull the discharge date from the substance use program from ('RDT'[Discharge Date])
I pull the last time the patient was seen at any other office for any other reason (i.e. free med program, weight managmentment program, family counseling, etc) from ('PD'[LastServiceDate]
I have the two tables linked by Patient ID #
I need help to either:
I do have a filter in place to only display patients with a Discharge Date.
Also note I have tried the below DAX for a new column and it hosed up the dates. (see the red lines)
The end goal is to have 2 displays.
First display is to add a column to the above matrix so it looks like this:
RDD LSD Days between dates
12/17/2019 1/17/2020 30
1/27/2020 7/27/2021 547
and a second display that's just a data card with the Average days for all patients.
Thanks
Solved! Go to Solution.
Hi @anwilkins ,
First, you can create a calculated column in PD table to get the Discharge Date from RDT table:
Imported RDT Date =
CALCULATE (
MAX ( 'RESET Diagnostics Tracker'[Discharge Date] ),
FILTER (
'RESET Diagnostics Tracker',
'RESET Diagnostics Tracker'[Patient ID #] = EARLIER ( 'PD'[Patient ID #] )
)
)
Then create a measure as below to get the days between Discharge Date of RDT table and Last Service Data of PD table:
Days between dates =
DATEDIFF (
SELECTEDVALUE ( 'PD'[Imported RDT Date] ),
SELECTEDVALUE ( 'PD'[LastServiceDate] ),
DAY
)
In addition, you can refer the content in the following links to get it.
CALCULATE DAYS BETWEEN TWO DATES / FIELDS IN DIFFERENT TABLES
If the above ones can't help you get the expected result, please provide some sample data with Text format(exclude sensitive data) and your expected result with specific examples and backend logic. Thank you.
Best Regards
Hi @anwilkins ,
First, you can create a calculated column in PD table to get the Discharge Date from RDT table:
Imported RDT Date =
CALCULATE (
MAX ( 'RESET Diagnostics Tracker'[Discharge Date] ),
FILTER (
'RESET Diagnostics Tracker',
'RESET Diagnostics Tracker'[Patient ID #] = EARLIER ( 'PD'[Patient ID #] )
)
)
Then create a measure as below to get the days between Discharge Date of RDT table and Last Service Data of PD table:
Days between dates =
DATEDIFF (
SELECTEDVALUE ( 'PD'[Imported RDT Date] ),
SELECTEDVALUE ( 'PD'[LastServiceDate] ),
DAY
)
In addition, you can refer the content in the following links to get it.
CALCULATE DAYS BETWEEN TWO DATES / FIELDS IN DIFFERENT TABLES
If the above ones can't help you get the expected result, please provide some sample data with Text format(exclude sensitive data) and your expected result with specific examples and backend logic. Thank you.
Best Regards