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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.