Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
Apologies if the title is not clear, I wasn;t sure how to word it!
I'm trying to create the following:
Patients can have 1 or more referrals to our health service. Each referral has a start date and an end date. If the referral is ongoing, the end date will be blank.
In a seperate table is a list of codes entered on their health record with the date of entry. I need to identify how many patients have been coded as either "Consent to treatment" or "Dissent to treatment" within 3 days of the referral start date.
It is complicated by the fact someone could have more than 1 referral as I need to identify correctly for each referral - so it needs to relate to the relevant referral start date.
Referral table
Pt ID | PtIDSite | Start Date | End Date |
123456 | 123456WingA | 12/04/21 | 15/05/21 |
987654 | 987654WingF | 01/05/21 | |
123456 | 123456WingA | 17/05/21 |
Code table
PtID | PtIDSite | Code | Date |
123456 | 123456WingA | XaJrC - Consent | 13/04/21 |
987654 | 987654WingF | XaJrE - Dissent | 01/05/21 |
From the example above, it should identify that 2 referrals have been coded within 3 days (note the 2nd referral for Pt 123456 should not achieve, as the code on 13th April should relate to the earlier referral only.
Many thanks for any light anyone could shed!
Solved! Go to Solution.
Hi @bigrods ,
According to your description, do you want to find the number of referrals that meet the starting time? I tried to do a test, refer to the following measure.
M =
CALCULATE (
COUNTROWS ( 'Referral table' ),
FILTER (
ALL ( 'Referral table' ),
'Referral table'[Pt ID] = MAX ( 'Referral table'[Pt ID] )
&& MAX ( 'Referral table'[Pt ID] ) = MAX ( 'Code table'[PtID] )
&& 0
<= ( 'Code table'[Date].[Day] - MAX ( 'Referral table'[Start Date].[Day] ) ) <= 3
)
)
If I fail to solve your problem, it may be that I did not fully understand what you mean. Can you describe detailed requirements and desired results? I will answer for you as soon as possible, looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @bigrods ,
According to your description, do you want to find the number of referrals that meet the starting time? I tried to do a test, refer to the following measure.
M =
CALCULATE (
COUNTROWS ( 'Referral table' ),
FILTER (
ALL ( 'Referral table' ),
'Referral table'[Pt ID] = MAX ( 'Referral table'[Pt ID] )
&& MAX ( 'Referral table'[Pt ID] ) = MAX ( 'Code table'[PtID] )
&& 0
<= ( 'Code table'[Date].[Day] - MAX ( 'Referral table'[Start Date].[Day] ) ) <= 3
)
)
If I fail to solve your problem, it may be that I did not fully understand what you mean. Can you describe detailed requirements and desired results? I will answer for you as soon as possible, looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Henry,
Many apologies for not replying; I got caught up with stuff, then it just went out of my head - your solution worked for me, thanks so much!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |