Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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 )
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
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.
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 )
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!
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
Hi Stachu,
This is definately moving in the right direction. So what you have done is calculating the difference in time from the last timestamp in one room vs the first timestamp in the next room (i.e. travel time), which is great, but it does not seem to work across the dates for all occations - not sure why?
Thanks!!
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 )
Thanks for this Stachu, this definately guided me in the right direction.
I've done the following, I've said in one calculated column, where Motion[Room] <> CurrentRoom and another where Motion[Room] = CurrentRoom - then used an IF statement to return the relevant value. I can then just sum the values as required and I have question 1 answered. Let me know if there is a cleaner way of doing this.
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 )
Travel Time 2 = 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 )
And finally...
Time Spent = IF ( FIRSTDATE ( Motion[Date] ) && ISBLANK ( Motion[Travel Time] ) && ISBLANK ( Motion[Travel Time 2] ), Motion[Time(no secs)], IF ( ISBLANK ( Motion[Travel Time] ), Motion[Travel Time 2], Motion[Travel Time] ) )
And then the count of motion censor triggers
Activity Count = CALCULATE ( COUNT ( Motion[Room] ), ALLEXCEPT ( Motion, Motion[Date], Motion[Location], Motion[Room] ) )
Any revisions will be appreciated, else I am a happy chappie 🙂
Thanks again!!
maybe I am oversimplyfying, but shouldn't Time Spent be just
Time Spent = SUM(Motion[Travel Time])
it excludes the travel time, and in case you want to add it you can just add the SUM(Motion[Travel Time 2])
I managed to get it to work perfectly - thanks for the input!!
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |