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
