Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Llewbear
Advocate I
Advocate I

Time 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:

RoomLocationStatusDateDateTimeTimeTime(no secs)
Living RoomLocation 1active2018/05/162018/05/16 00:1400:14:3500:14
Living RoomLocation 1active2018/05/162018/05/16 00:3100:31:0900:31
Living RoomLocation 1inactive2018/05/162018/05/16 17:4317:43:0417:43
Living RoomLocation 1inactive2018/05/162018/05/16 18:5218:52:4718:52
Living RoomLocation 1active2018/05/162018/05/16 19:3819:38:5219:38
Living RoomLocation 1inactive2018/05/162018/05/16 20:5320:53:0720:53
Living RoomLocation 1active2018/05/162018/05/16 21:1921:19:3021:19
FL6 BathroomLocation 1inactive2018/05/162018/05/16 21:3321:33:3821:33
FL6 BathroomLocation 1inactive2018/05/162018/05/16 21:3721:37:5021:37
Living RoomLocation 1inactive2018/05/162018/05/16 23:4523:45:1523:45
Living RoomLocation 1inactive2018/05/172018/05/17 10:0010:00:2410:00
Living RoomLocation 1active2018/05/172018/05/17 10:3410:34:2810:34
Living RoomLocation 1inactive2018/05/172018/05/17 11:0211:02:0811:02
Living RoomLocation 1active2018/05/172018/05/17 11:0911:09:5511:09
Living RoomLocation 1active2018/05/172018/05/17 11:2611:26:2611:26
Living RoomLocation 1active2018/05/172018/05/17 12:1612:16:3912:16
Living RoomLocation 1active2018/05/172018/05/17 12:2312:23:1312:23
Living RoomLocation 1inactive2018/05/172018/05/17 14:4514:45:0614:45
Living RoomLocation 1inactive2018/05/172018/05/17 15:5915:59:2315:59
Living RoomLocation 1active2018/05/172018/05/17 16:2116:21:0116:21
Living RoomLocation 1inactive2018/05/172018/05/17 17:2917:29:2017:29
Living RoomLocation 1inactive2018/05/172018/05/17 18:0018:00:3418:00
Living RoomLocation 1active2018/05/172018/05/17 18:0018:00:1818:00
Living RoomLocation 1inactive2018/05/172018/05/17 19:2119:21:3819:21
Living RoomLocation 1inactive2018/05/172018/05/17 20:4320:43:4320:43
Living RoomLocation 1active2018/05/172018/05/17 21:4121:41:3621:41
Living RoomLocation 1active2018/05/172018/05/17 21:5721:57:5921:57
Living RoomLocation 1active2018/05/172018/05/17 22:1722:17:4722:17
Living RoomLocation 1active2018/05/182018/05/18 08:1108:11:0208:11
Living RoomLocation 1active2018/05/182018/05/18 08:4608:46:5308:46
Living RoomLocation 1inactive2018/05/182018/05/18 09:0809:08:5909:08
Living RoomLocation 1active2018/05/182018/05/18 10:0610:06:1810:06
Living RoomLocation 1active2018/05/182018/05/18 11:0011:00:0211:00
Living RoomLocation 1active2018/05/182018/05/18 11:5111:51:1811:51
Living RoomLocation 1inactive2018/05/182018/05/18 11:5811:58:2611:58
Living RoomLocation 1inactive2018/05/182018/05/18 16:2616:26:4916:26
Living RoomLocation 1active2018/05/182018/05/18 18:4418:44:4718:44
Living RoomLocation 1inactive2018/05/182018/05/18 18:5018:50:3718:50
Living RoomLocation 1inactive2018/05/182018/05/18 18:5718:57:2018:57
Living RoomLocation 1inactive2018/05/182018/05/18 19:0019:00:1719:00
Living RoomLocation 1inactive2018/05/182018/05/18 19:0419:04:1719:04
Living RoomLocation 1inactive2018/05/182018/05/18 19:0919:09:4619:09
FL6 BathroomLocation 1inactive2018/05/182018/05/18 21:1621:16:4521:16
FL6 BathroomLocation 1active2018/05/182018/05/18 21:2121:21:2321:21
Living RoomLocation 1inactive2018/05/192018/05/19 01:0901:09:4801:09
Living RoomLocation 1inactive2018/05/192018/05/19 01:1601:16:0501:16
Living RoomLocation 1inactive2018/05/192018/05/19 01:2201:22:1401:22
Living RoomLocation 1inactive2018/05/192018/05/19 10:0810:08:0710:08
Living RoomLocation 1inactive2018/05/192018/05/19 10:2810:28:3810:28
Living RoomLocation 1active2018/05/192018/05/19 10:2810:28:2610:28
Living RoomLocation 1inactive2018/05/192018/05/19 14:3714:37:3314:37
Living RoomLocation 1inactive2018/05/192018/05/19 14:5914:59:1314:59
Living RoomLocation 1inactive2018/05/192018/05/19 17:5917:59:0417:59
Living RoomLocation 1inactive2018/05/192018/05/19 18:2418:24:3918:24
Living RoomLocation 1inactive2018/05/192018/05/19 20:3920:39:3220:39
Living RoomLocation 1active2018/05/192018/05/19 22:0222:02:0322:02
Living RoomLocation 1active2018/05/192018/05/19 23:4523:45:2823:45

 

Any guidance you can give in this regard would be very much appreciated!!

 

Thank you and have a wonderful day.

 

Kind Regards,

LlewBear

1 ACCEPTED SOLUTION
Stachu
Community Champion
Community Champion

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
    )


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

10 REPLIES 10
v-jiascu-msft
Employee
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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

Stachu
Community Champion
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
    )





Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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
Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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

Stachu
Community Champion
Community Champion

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
    )


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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!!

 

Stachu
Community Champion
Community Champion

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])



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

I managed to get it to work perfectly - thanks for the input!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.