Hospital bed occupancy per ward

[Infrecon]

Hello together

I am relatively new to Power BI, have worked a lot with Tableau.

I have the following tables:

Case Data:

Case_ID	Admission date and time	Discharge date and time	Ward	...
2021_KSSS20313324	01.09.2021 11:30	11.09.2021 13:00	S32
2021_KSSS20313124	29.08.2021 07:00	31.08.2021 11:00	S11

Ward statistics

Date	Ward	Occupiable beds
29.08.2021	S32	35
29.08.2021	S11	18
30.08.2021	S32	35

Now to my question. There are many other parameters in the case data (e.g. length of stay, age, etc.).

1. how do I have to set up the data model? I started with a Dim_Date table and a Dim_Time table, because the data set covers several years.
How do I link the two tables Case Data and Bed Statistics? I tried it with a many:1 relationsship on departments, but it always tells me only many:many possible

2. how can i show the following analysis in a matrix table:

What is the average number of patients per hour in the day, versus the average number of beds available, that by wards.

Ward	0	1	2	3	4	5	..
S35	12	12	12	12	12	12	..

Sorry I struggle a bit just now.

[v-yalanwu-msft]

Hi, @Infrecon ;

I tried your table, but it is not very clear how your 12 values for 0-12 hours come from. Can you make a simple example and the results you want to output? This will allow me to better understand. Will the hourly average of another day be displayed every day? Then the vision is bigger.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Infrecon]

Hi, @v-yalanwu-msft 

Thanks for your support. Attached you'll find a dummy project with a certain amount of dummy datas. So far i have added the Dim_Date and Dim-Time Table.  But currently unsure about the relationsship.

From the data you can see what parameters are available. I would now like to visualize the following representation:

How many patients are on the corresponding ward per time unit on average. This time unit should be dynamic. That is, I can either display it per day, per year or per hour.

Attached is an example on the days:

Patients per day

	05.01.2021	06.01.2021	07.01.2021
S12	3	6	7
S32	4	6	7
S44	2	3	4

Number of occupiable beds per day

	05.01.2021	06.01.2021	07.01.2021
S12	16	16	16
S32	13	28	12
S44	19	23	12

Average occupancy per day

	05.01.2021	06.01.2021	07.01.2021
S12	18.8%	37.5%	43.8%
S32	30.8%	21.4%	58.3%
S44	10.5%	13.0%	33.3%

Also, you should be able to show a progression, for example, in the following way:

Case_ID	03.01.2021	04.01.2021	05.01.2021	06.01.2021	07.01.2021	08.01.2021	09.01.2021	10.01.2021
2021_SCAS1231231	1	1	1	1	1	1	1	1
2021_SCAS12315834	1	1	1	1	1	1
2021_SCAS18412512		1	1	1	1	1	1	1
2021_SCAS84234015		1	1	1	1	1	1	1

[YukiK]

Firstly, I recommend spliting the datetime column in case data table into date and time, so as to connect to both date dim table and time dim table.

When you said you tried to create a relationship on department, I assume you meant ward. Ward statistics table is like an aggregate dim table and it looks like its grain is per row per date + ward. You can't make a relationship on multiple columns in power bi, so you'd want to a key column to ward statistics table that defines each row uniquely, and then join that table on to the case data table on date and ward columns and only leave the key column of ward statistics table in the case data table so that you can join these tables on this key column. This should allow you to make a one-to-many relationship between these tables. And create relationships between case data and date table and time table.

To create a matrix table you mentioned, you bring in the matrix visual wherein you put ward on the rows and hours on the columns, and your measure on to the values.

Hope this helps!

[YukiK]

Another way is to create a table that contains each ward and this table will be used as a bridge table between case data table and ward statistics table.