Forum Discussion
Advocate ITime Spent in each room
Good day,
I have a similar to THIS POST, where I'm also trying to address with ALLEXCEPT(), however mine has a little twist.
I'm trying to answer the following questions:
1 What room is the person in (Assuming there is only one person in the house)?
2 How long has he/she been in this room?
3. Are they active or idle within this room (count of sensor activity)?
I thought that 1 may be solved with some timestamped calculated column that updates when a motion sensor triggers in a new room. For 2 Might be the DateDiff between first and last time the sensor triggered and 3 Might be a simple Count of motion sensor events during the period.
So the logic I was trying to follow was to calculate the MIN and MAX of the DateTime for each room, from each location (there are more than one location in the data and then measuring the difference between the min and max times.
NB!! This approach unfortunately doesn't take into account, the time spent when moving from one room to another.
When I use the calculation: Motion Sensor MIN = CALCULATE(MIN ( Motion[Time(no secs)]), ALLEXCEPT ( Motion, Motion[Date], Motion[Location], Motion[Room] )), it does not take into account when a person goes from a Living Room to a Bathroom back to the Living Room.
My data is as follows:
| Room | Location | Status | Date | DateTime | Time | Time(no secs) |
| Living Room | Location 1 | active | 2018/05/16 | 2018/05/16 00:14 | 00:14:35 | 00:14 |
| Living Room | Location 1 | active | 2018/05/16 | 2018/05/16 00:31 | 00:31:09 | 00:31 |
| Living Room | Location 1 | inactive | 2018/05/16 | 2018/05/16 17:43 | 17:43:04 | 17:43 |
| Living Room | Location 1 | inactive | 2018/05/16 | 2018/05/16 18:52 | 18:52:47 | 18:52 |
| Living Room | Location 1 | active | 2018/05/16 | 2018/05/16 19:38 | 19:38:52 | 19:38 |
| Living Room | Location 1 | inactive | 2018/05/16 | 2018/05/16 20:53 | 20:53:07 | 20:53 |
| Living Room | Location 1 | active | 2018/05/16 | 2018/05/16 21:19 | 21:19:30 | 21:19 |
| FL6 Bathroom | Location 1 | inactive | 2018/05/16 | 2018/05/16 21:33 | 21:33:38 | 21:33 |
| FL6 Bathroom | Location 1 | inactive | 2018/05/16 | 2018/05/16 21:37 | 21:37:50 | 21:37 |
| Living Room | Location 1 | inactive | 2018/05/16 | 2018/05/16 23:45 | 23:45:15 | 23:45 |
| Living Room | Location 1 | inactive | 2018/05/17 | 2018/05/17 10:00 | 10:00:24 | 10:00 |
| Living Room | Location 1 | active | 2018/05/17 | 2018/05/17 10:34 | 10:34:28 | 10:34 |
| Living Room | Location 1 | inactive | 2018/05/17 | 2018/05/17 11:02 | 11:02:08 | 11:02 |
| Living Room | Location 1 | active | 2018/05/17 | 2018/05/17 11:09 | 11:09:55 | 11:09 |
| Living Room | Location 1 | active | 2018/05/17 | 2018/05/17 11:26 | 11:26:26 | 11:26 |
| Living Room | Location 1 | active | 2018/05/17 | 2018/05/17 12:16 | 12:16:39 | 12:16 |
| Living Room | Location 1 | active | 2018/05/17 | 2018/05/17 12:23 | 12:23:13 | 12:23 |
| Living Room | Location 1 | inactive | 2018/05/17 | 2018/05/17 14:45 | 14:45:06 | 14:45 |
| Living Room | Location 1 | inactive | 2018/05/17 | 2018/05/17 15:59 | 15:59:23 | 15:59 |
| Living Room | Location 1 | active | 2018/05/17 | 2018/05/17 16:21 | 16:21:01 | 16:21 |
| Living Room | Location 1 | inactive | 2018/05/17 | 2018/05/17 17:29 | 17:29:20 | 17:29 |
| Living Room | Location 1 | inactive | 2018/05/17 | 2018/05/17 18:00 | 18:00:34 | 18:00 |
| Living Room | Location 1 | active | 2018/05/17 | 2018/05/17 18:00 | 18:00:18 | 18:00 |
| Living Room | Location 1 | inactive | 2018/05/17 | 2018/05/17 19:21 | 19:21:38 | 19:21 |
| Living Room | Location 1 | inactive | 2018/05/17 | 2018/05/17 20:43 | 20:43:43 | 20:43 |
| Living Room | Location 1 | active | 2018/05/17 | 2018/05/17 21:41 | 21:41:36 | 21:41 |
| Living Room | Location 1 | active | 2018/05/17 | 2018/05/17 21:57 | 21:57:59 | 21:57 |
| Living Room | Location 1 | active | 2018/05/17 | 2018/05/17 22:17 | 22:17:47 | 22:17 |
| Living Room | Location 1 | active | 2018/05/18 | 2018/05/18 08:11 | 08:11:02 | 08:11 |
| Living Room | Location 1 | active | 2018/05/18 | 2018/05/18 08:46 | 08:46:53 | 08:46 |
| Living Room | Location 1 | inactive | 2018/05/18 | 2018/05/18 09:08 | 09:08:59 | 09:08 |
| Living Room | Location 1 | active | 2018/05/18 | 2018/05/18 10:06 | 10:06:18 | 10:06 |
| Living Room | Location 1 | active | 2018/05/18 | 2018/05/18 11:00 | 11:00:02 | 11:00 |
| Living Room | Location 1 | active | 2018/05/18 | 2018/05/18 11:51 | 11:51:18 | 11:51 |
| Living Room | Location 1 | inactive | 2018/05/18 | 2018/05/18 11:58 | 11:58:26 | 11:58 |
| Living Room | Location 1 | inactive | 2018/05/18 | 2018/05/18 16:26 | 16:26:49 | 16:26 |
| Living Room | Location 1 | active | 2018/05/18 | 2018/05/18 18:44 | 18:44:47 | 18:44 |
| Living Room | Location 1 | inactive | 2018/05/18 | 2018/05/18 18:50 | 18:50:37 | 18:50 |
| Living Room | Location 1 | inactive | 2018/05/18 | 2018/05/18 18:57 | 18:57:20 | 18:57 |
| Living Room | Location 1 | inactive | 2018/05/18 | 2018/05/18 19:00 | 19:00:17 | 19:00 |
| Living Room | Location 1 | inactive | 2018/05/18 | 2018/05/18 19:04 | 19:04:17 | 19:04 |
| Living Room | Location 1 | inactive | 2018/05/18 | 2018/05/18 19:09 | 19:09:46 | 19:09 |
| FL6 Bathroom | Location 1 | inactive | 2018/05/18 | 2018/05/18 21:16 | 21:16:45 | 21:16 |
| FL6 Bathroom | Location 1 | active | 2018/05/18 | 2018/05/18 21:21 | 21:21:23 | 21:21 |
| Living Room | Location 1 | inactive | 2018/05/19 | 2018/05/19 01:09 | 01:09:48 | 01:09 |
| Living Room | Location 1 | inactive | 2018/05/19 | 2018/05/19 01:16 | 01:16:05 | 01:16 |
| Living Room | Location 1 | inactive | 2018/05/19 | 2018/05/19 01:22 | 01:22:14 | 01:22 |
| Living Room | Location 1 | inactive | 2018/05/19 | 2018/05/19 10:08 | 10:08:07 | 10:08 |
| Living Room | Location 1 | inactive | 2018/05/19 | 2018/05/19 10:28 | 10:28:38 | 10:28 |
| Living Room | Location 1 | active | 2018/05/19 | 2018/05/19 10:28 | 10:28:26 | 10:28 |
| Living Room | Location 1 | inactive | 2018/05/19 | 2018/05/19 14:37 | 14:37:33 | 14:37 |
| Living Room | Location 1 | inactive | 2018/05/19 | 2018/05/19 14:59 | 14:59:13 | 14:59 |
| Living Room | Location 1 | inactive | 2018/05/19 | 2018/05/19 17:59 | 17:59:04 | 17:59 |
| Living Room | Location 1 | inactive | 2018/05/19 | 2018/05/19 18:24 | 18:24:39 | 18:24 |
| Living Room | Location 1 | inactive | 2018/05/19 | 2018/05/19 20:39 | 20:39:32 | 20:39 |
| Living Room | Location 1 | active | 2018/05/19 | 2018/05/19 22:02 | 22:02:03 | 22:02 |
| Living Room | Location 1 | active | 2018/05/19 | 2018/05/19 23:45 | 23:45:28 | 23:45 |
Any guidance you can give in this regard would be very much appreciated!!
Thank you and have a wonderful day.
Kind Regards,
LlewBear
I assumed that travel time is only relevant if done the same day. If you remove the criteria of the same day, then it will work across days as well e.g. night 18/19 of May
Travel Time = VAR CurrentRoom = Motion[Room] VAR CurrentLocation = Motion[Location] VAR CurrentDate = Motion[Date] VAR CurrentTimeStamp = Motion[DateTime] VAR PreviousTimeStamp = CALCULATE ( MAX ( Motion[DateTime] ), ALL ( Motion ), Motion[DateTime] < CurrentTimeStamp ) VAR PreviousRoomTimeStamp = CALCULATE ( MAX ( Motion[DateTime] ), FILTER ( ALL ( Motion ), Motion[DateTime] = PreviousTimeStamp && Motion[Location] = CurrentLocation && Motion[Room] <> CurrentRoom ) ) RETURN IF ( PreviousRoomTimeStamp <> BLANK (), CurrentTimeStamp - PreviousRoomTimeStamp )
10 Replies
- Stachu
Community Champion
regarding 2) - can you add calculated column for the travel time, and then subtract it from the time in the room formula?
Travel Time = VAR CurrentRoom = Motion[Room] VAR CurrentLocation = Motion[Location] VAR CurrentDate = Motion[Date] VAR CurrentTimeStamp = Motion[DateTime] VAR PreviousTimeStamp = CALCULATE ( MAX ( Motion[DateTime] ), ALL ( Motion ), Motion[DateTime] < CurrentTimeStamp ) VAR PreviousRoomTimeStamp = CALCULATE ( MAX ( Motion[DateTime] ), FILTER ( ALL ( Motion ), Motion[Date] = CurrentDate && Motion[DateTime] = PreviousTimeStamp && Motion[Location] = CurrentLocation && Motion[Room] <> CurrentRoom ) ) RETURN IF ( PreviousRoomTimeStamp <> BLANK (), CurrentTimeStamp - PreviousRoomTimeStamp )- Llewbear
Good day Stachu,
Thank you for the info. I've tried this formula but it returns no result. I understand the logic you are putting across but it doesn't seem to work.
Thanks!
- Stachu
Hi
have you created calculated column? This syntax will not work as a measure
see the example file here
https://1drv.ms/u/s!AjxUGXgGNzCEiVPvfHe5L_OYRkCx
v-jiascu-msftMicrosoft Employee
Hi LlewBear,
To be honest, the calculations here could be easy. The difficult part could be the logic. Can you share us the steps to solve this with just one day's data? They are all date time points rather than time durations. So how to get the results by plain math formula. Then we can convert it into DAX formula.
Room Location Status Date DateTime Time Time(no secs) Living Room Location 1 active 2018/05/16 2018/05/16 00:14 00:14:35 00:14 Living Room Location 1 active 2018/05/16 2018/05/16 00:31 00:31:09 00:31 Living Room Location 1 inactive 2018/05/16 2018/05/16 17:43 17:43:04 17:43 Living Room Location 1 inactive 2018/05/16 2018/05/16 18:52 18:52:47 18:52 Living Room Location 1 active 2018/05/16 2018/05/16 19:38 19:38:52 19:38 Living Room Location 1 inactive 2018/05/16 2018/05/16 20:53 20:53:07 20:53 Living Room Location 1 active 2018/05/16 2018/05/16 21:19 21:19:30 21:19 FL6 Bathroom Location 1 inactive 2018/05/16 2018/05/16 21:33 21:33:38 21:33 FL6 Bathroom Location 1 inactive 2018/05/16 2018/05/16 21:37 21:37:50 21:37 Living Room Location 1 inactive 2018/05/16 2018/05/16 23:45 23:45:15 23:45 Best Regards,
Dale
- Llewbear
Good day v-jiascu-msft,
Let me try put the logic across.
There is one person living in this house.
As per my previous message, I'm trying to answer 2 main questions - let me try ellobrate:
1. How much time is spent by this person in each room everyday (not aggregated by day but by time period in room). So I want to be able to calculate how much time he/she spent from the time when the first motion sensor triggered for the day (First time stamp for the day) in a particular room. How much time passes while this person is in that room? Then when the person moves from one room to another, it should again calculate the difference between the first time stamp for the sensor of the new room and the first sensor trigger of the next room.
2. While a person is in a particular room, the sensor goes off every time a person moves - here I want to know hw many times the sensor was activated while the person was in that room for that specific period of time.
Logic 1
So my logic is telling me that for 1 above I need to find the min and max time the person spent in each room within a period of time. The period of time being the first time the sensor went off and that room, and the first time the sensor went off in another room when the person moved to a new room.
Logic 2
For 2 above, it should just be the count of how many times the sensor was triggered while in the room between the min and max as defined in Logic 1 above.
I hope this makes more sense.
Thanks again.
