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
MattRo
Frequent Visitor

How to compare dates from two related tables and generate accuracy rate

Hello. I work in a hospital and we want to compare the actual discharge date of a patient with accuracy of our estimate from earlier. I have two tables related (1:many) by a patient ID number (MRN).

 

Table 1 - Yesterdays DCs

  • MRN
  • Disch Date

Table 2 - DC Info - This table give patient information on a daily basis. There are multiple/duplicate MRN in this table and I am interested in Expected Dischrg when Report Date/Time is latest.

  • MRN
  • Expected Dischrg
  • Report Date/Time

Can you help me with a measure that will give the percentage of MRNs that have the correct Expected Dischrg date?

 

Example:

Below, you can see, on 3/24, we discharged 9 patients where we had predicted they would discharge on 3/24. And there where 20 patients discharged where we had the estimate wrong.

MattRo_3-1679967835221.png

Side note - Not to sidetrack, but a second question...I think the column above is misleading or wrong, "Latest Expected Dischrg" is not the same as "last reported" Expected Dischrg which would be more accturate.

 

Thank you!

3 REPLIES 3
amitchandak
Super User
Super User

@MattRo , You need have common MRN dimension, post that you need have measures

 

measure =

var _diff = datediff(max(Table1[DesChg]), max(Table2[Expected DesChg]), day)

return

countx(values(MRN[MRN]), if(_diff  <>0 , [MRN], blank() ) )

 

Now you can divide ot with countrows(filter(Table, Table1[DesChg] <> Blank()))

@amitchandak 
Using your measure, I managed to come up with this which identifies the matches. I think I am on the right track, but I am not able to sum. Any ideas?

 

MattRo_2-1680050050119.png

 

using this measure:

EDD Accurate? =
var _diff = datediff(max('Yesterday DCs'[Disch Date]), max('DC info'[Expected Dischrg]), day)
return
countx(
    values('Yesterday DCs'[MRN.DCd]),
    if(_diff  <>0 , blank(), 1)
)

@amitchandak  Hi. Thanks for the response! Could you elaborate on "You need to have a common MRN dimension"? Do you mean I need to create a separate table containing MRN only? What would the purpose be given that I already have a table with a column of all unique MRN values? Thank you.

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.

Top Solution Authors