Forum Discussion
Creating a hospital census dashboard
All,
I'm not sure if this is the correct place to ask this question but I'm looking for help on creating a hospital census dashboard. I want to use a matrix visualization to show which patients were present at a particular hospital on a given date.
Sample data set
Patient A admitted on 1/1/2021 and discharged on 1/2/2021
Patient B admitted on 1/1/2021 and discharged on 1/3/2021
Patient C admitted on 1/2/2021 and discharged on 1/3/2021
Patient D admitted on 1/2/2021 and discharged on 1/5/2021
Patient E admitted on 1/3/2021 and discharged on 1/4/2021
The matrix visualization should then display
Date
1/1/2021
Patient A
Patient B
1/2/2021
Patient A
Patient B
Patient C
Patient D
1/3/2021
Patient B
Patient C
Patient D
Patient E
1/4/2021
Patient D
Patient E
1/5/2021
Patient D
1/6/2021
No Patients
I can't figure out how to include a patient visit if the date being evaluated falls between the admit and discharge dates for that visit. Can anyone help me figure this out?
Thank you,
Ron
Hi ErDrRon ,
1.When you import the data into Power BI, it looks like this in the Power Query Editor.
2.Right-click the field name and select Split by positions.
3.It will intelligently figure out where you want to split, we remove the 8 and click ok.
3.Remove unnecessary columns and rename other columns. Click Close&Apply.
4.Create a calendar table. No relationships between tables.
Table 2 = CALENDAR(DATE(2021,1,1),DATE(2021,1,10))5.Create the measure and put into the matrix.
Measure = IF( MAX('Table'[Admitted Date]) <= MAX ( 'Table 2'[Date] ) && MAX('Table'[Discharged Date]) >= MAX ( 'Table 2'[Date] ) ,"In Hospital" )Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
First, get the source data in tabular form(3 columns).
Then there are 2 ways to go about this:
1. generate/expand the table to hold a row for each date that a patient is in hospital.
Pull the date and patient columns on to a matrix (in Rows)
OR
2. Create a Dates table. Don't create a relationship to the source table.
Write a measure to Count the patients with Admitted Date <= Date and Discharged Date >= Date.Drag Date from the date table on to a matrix (in Rows). Put Patient in Rows.
Put Measure in Values.
Hi HotChilli,
trying to write a measure, but failed, I am a newbie..
Is there any way of getting the DAX if you don't mind?
also what if Patient A has another admission (ex, 01/03/21) and discharge (ex, 01/05/21)?
Thanks for your contribution!
Hi ErDrRon ,
1.When you import the data into Power BI, it looks like this in the Power Query Editor.
2.Right-click the field name and select Split by positions.
3.It will intelligently figure out where you want to split, we remove the 8 and click ok.
3.Remove unnecessary columns and rename other columns. Click Close&Apply.
4.Create a calendar table. No relationships between tables.
Table 2 = CALENDAR(DATE(2021,1,1),DATE(2021,1,10))5.Create the measure and put into the matrix.
Measure = IF( MAX('Table'[Admitted Date]) <= MAX ( 'Table 2'[Date] ) && MAX('Table'[Discharged Date]) >= MAX ( 'Table 2'[Date] ) ,"In Hospital" )Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
awesome Stephen,
quick question.. is there any way of grouping monthly?
thanks
