Forum Discussion
Creating a hospital census dashboard
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.
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!
