topic Re: Calculating % of Patients Not Seen by Date in DAX Commands and Tips

Calculating % of Patients Not Seen by Date

rmcmullenNSHOA — Wed, 20 Jul 2022 22:31:58 GMT

I'm trying to create a report that shows, for a given range of dates (e.g., 5/18/22 - 7/19/22), how many patients were in a hospital and able to be seen, how many were seen, and how many were not seen. Finally, I want to add a column showing the % that were not seen. The final result should be broken out by each distinct date in the range.

Simple enough to calculate - the struggle is getting the data in a place to get me there.

I've shared a sample data source file here.

So far, I've created a calendar table based on the minimum Admit date and the maximum Discharge date and created a relationship to the dDateTime field.

I also created a "Patient Count" measure Patient Count = DISTINCTCOUNT(VisitData[sPatientNumber]), but that's as far as I've gotten.

Any help would be greatly appreciated.

Re: Calculating % of Patients Not Seen by Date

amitchandak — Thu, 21 Jul 2022 03:58:38 GMT

rmcmullenNSHOA , Not seen

countx(values(VisitData[sPatientNumber]), if(isblank([Patient Count]),[sPatientNumber], blank() ) )

Re: Calculating % of Patients Not Seen by Date

rmcmullenNSHOA — Thu, 21 Jul 2022 18:54:16 GMT

So I ended up solving this in excel for now. Here's how I did it:

New tab, cell A1 = UNIQUE(Table1[sLastName]:[Discharge]). This gives me all the distinct stays by patient (i.e., if I was admitted/discharged multiple times during the period, they will show up separately).
Calculate the Length of Stay for each row = [Discharge] - [Admit] + 1
Calculate the number of times the patient was seen
= COUNTIFS(Table1[sPatientNumber],B2,Table1[Admit], C2)
Calculate the number of times the patient was not seen (i.e., Unseen)
= Length of Stay - Seen (from above)
Calculate the Unseen %
= Unseen / Length of Stay

I'd love to figure out how to do this in PowerBI and create a report that I could share with others on my team.

Re: Calculating % of Patients Not Seen by Date

Anonymous — Tue, 26 Jul 2022 09:56:33 GMT

Hi rmcmullenNSHOA ,

Would you please share the excel file with the above?

Thanks in advance!

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data