Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
To summize, I would like to create a visual which shows me at an hourly level, how many people are in the medical clinic. This has been done in excel but the requirements are now to have this visual on a power bi report which is connected to our database.
I have an activity table which shows for each row, details on a person's visit to the medical clinic.
Activity Table
ID: an identifier for a particular visit
StartDateID: the date at which the person arrives
StartTimeID: the hour at which the person arrives
EndDateID: the date at which the person leaves
EndTimeID: the time at which person leaves
**Other columns containing information about the person: these are things like age group ethnicities etc. I would like to filter by these attributes once we have the chart output.
Figure 1. Activity Table (where each row represents one person's visit and their arrival and leave date times, there are other columns which capture more details about the person e.g. age group )
ActivityID | EndDateID | StartTimeID | EndDateID | EndTImeID | Age Group |
E2267333 | 20181029 | 140000 | 20181029 | 170000 | Elderly |
E2267453 | 20181029 | 190000 | 20181029 | 220000 | Non Elderly |
E2267218 | 20181029 | 110000 | 20181029 | 220000 | Elderly |
E2267473 | 20181029 | 200000 | 20181029 | 230000 | Elderly |
E2267463 | 20181029 | 200000 | 20181029 | 230000 | Elderly |
E2267578 | 20181029 | 140000 | 20181029 | 140000 | No Elderly |
E2264674 | 20181029 | 40000 | 20181029 | 180000 | Elderly |
E2267411 | 20181029 | 170000 | 20181030 | 0 | Non Elderly |
E2267466 | 20181029 | 200000 | 20181029 | 200000 | Elderly |
E2267446 | 20181029 | 190000 | 20181029 | 190000 | No Elderly |
E2267404 | 20181029 | 170000 | 20181029 | 170000 | Elderly |
E2267370 | 20181029 | 150000 | 20181029 | 150000 | Non Elderly |
E1971746 | 20160817 | 90000 | 20160817 | 100000 | Elderly |
E1971768 | 20160817 | 100000 | 20160817 | 110000 | No Elderly |
E1981911 | 20160915 | 120000 | 20160915 | 180000 | Elderly |
E1981951 | 20160915 | 140000 | 20160915 | 170000 | Non Elderly |
E1981960 | 20160915 | 150000 | 20160915 | 200000 | Elderly |
E1982000 | 20160915 | 170000 | 20160915 | 180000 | No Elderly |
E1982005 | 20160915 | 170000 | 20160915 | 180000 | Elderly |
E1982008 | 20160915 | 170000 | 20160915 | 200000 | Non Elderly |
E1982032 | 20160915 | 190000 | 20160915 | 200000 | Elderly |
The databse is set up in that the four date time related columns in the activity table are joined to date and time dimension tables as per below:
Figure 2: Activity Relationship table to dimension tables
So basically, I want to show an output at any given day, how many people are in the medical centre at any hour of the day.
E.g. If a person arrives on the 23rd Sep 2018 at 8am and leaves the centre at 4pm in the afternoon, then between 8am and 4pm, the would be counted as 1 in during those hours. Each person from the activity table should be counted like this so the final output is an aggregate of people in the centre at every hour by day.
In the report output, the table should look like this:
Figure 3. Report Output which sums up the number of people in the medical centre based at each hour of day and date (based on the start Datetime and End DateTime)
Census DateTime | CalendarDate | Census | Occupancy |
1/07/2017 0:00 | 1/07/2017 | 12:00:00 AM | 29 |
1/07/2017 1:00 | 1/07/2017 | 1:00:00 AM | 26 |
1/07/2017 2:00 | 1/07/2017 | 2:00:00 AM | 22 |
1/07/2017 3:00 | 1/07/2017 | 3:00:00 AM | 20 |
1/07/2017 4:00 | 1/07/2017 | 4:00:00 AM | 19 |
1/07/2017 5:00 | 1/07/2017 | 5:00:00 AM | 20 |
1/07/2017 6:00 | 1/07/2017 | 6:00:00 AM | 19 |
1/07/2017 7:00 | 1/07/2017 | 7:00:00 AM | 14 |
1/07/2017 8:00 | 1/07/2017 | 8:00:00 AM | 17 |
1/07/2017 9:00 | 1/07/2017 | 9:00:00 AM | 20 |
1/07/2017 10:00 | 1/07/2017 | 10:00:00 AM | 25 |
1/07/2017 11:00 | 1/07/2017 | 11:00:00 AM | 27 |
1/07/2017 12:00 | 1/07/2017 | 12:00:00 PM | 33 |
1/07/2017 13:00 | 1/07/2017 | 1:00:00 PM | 31 |
1/07/2017 14:00 | 1/07/2017 | 2:00:00 PM | 35 |
1/07/2017 15:00 | 1/07/2017 | 3:00:00 PM | 35 |
1/07/2017 16:00 | 1/07/2017 | 4:00:00 PM | 31 |
1/07/2017 17:00 | 1/07/2017 | 5:00:00 PM | 34 |
1/07/2017 18:00 | 1/07/2017 | 6:00:00 PM | 33 |
1/07/2017 19:00 | 1/07/2017 | 7:00:00 PM | 39 |
1/07/2017 20:00 | 1/07/2017 | 8:00:00 PM | 42 |
1/07/2017 21:00 | 1/07/2017 | 9:00:00 PM | 45 |
1/07/2017 22:00 | 1/07/2017 | 10:00:00 PM | 37 |
1/07/2017 23:00 | 1/07/2017 | 11:00:00 PM | 39 |
This should then allow me to create a visual as per below:
Figure 4. Chart output where:
X- Axis = Date field and then time field (from the date and time column in figure 3) . The date component should be able to be filtered. For example I only want to look at a different 7 day range.
Y-Axis: the occupancy of the people in medical centre by hour of day.
Appreciate if you could help me out. I'm new to power bi and this output chart has been done in excel. The requirement for my work is now to migrate a lot of our visuals to power bi dashboard.
I understand that I may have to create some sort of date time dimension table for this visual but a bit stuck as to how to do this (since date and time are separated).
Also would appreciate how I would actually create the occupancy measure. In Excel I have used a COUNTIFs formula looking at the Census DateTime column in figure 3 and seeing whether a patient's StartDatetime is less than Census DateTime and EndDateTime is greater than census time.
Excel Formula:
=COUNTIFS(StartDateTime,<=CensusDateTime,EndDateTime,>=CensusDateTime)
Let me know if any further clarifications are needed. THanks in advance.
Solved! Go to Solution.
Hi @hermskies,
there are always more than one solution. I recommend to use only one dim date table which contains dates and hours of a day. This dim table won't be small (which is a disadvantage) but you can use it direct for your visuals.
What to do:
Hi @hermskies,
there are always more than one solution. I recommend to use only one dim date table which contains dates and hours of a day. This dim table won't be small (which is a disadvantage) but you can use it direct for your visuals.
What to do:
Dear Nolock,
I am very new to Power Bi and Power Query, I could not make the ponts 3 working. Could you please have a sample of the power bi file on that points?
Best regards,
Veasna
@Nolock
Hey Nolock, I'm trying to reproduce your steps to calculate some occupancy rate for chargingstations, which have 2 sockets.
Only problem with my data is that I don't have a hourly timestamp, rather just a given start and end time.
for example:
Table 1
Start_Time End_Time Duration Charge_ID_socket
1-1-2019 14:14:23 2-1-2019 15:14:33 25:00:10 P1234-1
1-1-2019 13:15:15 1-1-2019 23:32:25 10:17:10 P1234-2
.
.
etc.
I have a dim date table with the min and max date from start and end time, step 3 and 4 are the problem which I cant figure out. Note I'm a newb to Power BI and DAX, so it might be a silly question yet I cant figure it out...
Finaly visualizing the occupancy rate for each charging station + socket individually would be the end goal here. Can you help me out here?
Kind regards!
Hi @Anonymous,
however the old solution is correct, nowadays I would solve it in another way because I know much more about PowerQuery.
The simple solution is shown in the following code incl. sample data. You generate a list of hours between 2 timestamps and then expand the list into rows.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLRMzDXMzIwtFQwMrAyMLcyNFDSUTIyhYsamFoZmFgZGCvFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"Start", type datetime}, {"End", type datetime}}), HoursBetween = Table.AddColumn( ChangedType, "HoursBetweenList", (row) => List.Generate( () => Date.StartOfDay(row[Start]) + #duration(0, Time.Hour(row[Start]) + 1, 0, 0), each _ <= row[End], each _ + #duration(0, 1, 0, 0) ) ), ExpandList = Table.ExpandListColumn(HoursBetween, "HoursBetweenList") in ExpandList
@Nolock
Thank you for your reply, however I'm still not getting a hourly list between start and endtime for each session. I must be doing something wrong here. Tried copy paste your code into an empty query which doesn't seem to be working. Any other way of showing me how it's done?
After that I should be able to link my Fact table with this hourly list to my dim date table, right?
Hi @Anonymous,
I've checked the code again and it works as it should. Do you see the same screenshot when you execute the query?
I got it to work! Now all I need is to have this list alongside my activity/fact table that contains all my sessions, so it will probably quadruple in size but then I can visualize occupancy?
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |