Creating a hospital census dashboard

[ErDrRon]

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

[v-stephen-msft · Accepted solution]

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.

[mudzbee]

awesome Stephen, 

quick question.. is there any way of grouping monthly?

thanks

[HotChilli]

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.

[mudzbee]

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!