Identify rows belonging together based on field value changes

[masterl1983]

Hi,

Maybe the subject isn't that good but it's hard to describe my problem in on sentence.

I'm analyzing multiple IoT Devices which do send every few hours their current geo location.

The travelled path of those devices is (more or less) always a circle. So you can say that they start at 12 o'clock -> travel to 6 o'clock -> travel back to 12 o'clock.

I have a table which looks like this one. A new Trip starts when CurrentLocation changes from Location_A to something else.

A Trip ends when currentLocation changes back to LocationA.

I addition I need to get the the time (hours) which indicates how long a Device is within the current location.

So my initial table looks like

DateTime Device ID CurrentLocation Distinct Trip No. Duration in Location

01.01.2017 06:00	ID0001	Location_XYZ
01.01.2017 08:00	ID0001	Location_A
02.01.2017 10:00	ID0001	Location_A
03.01.2017 12:00	ID0001	Location_C
04.01.2017 12:00	ID0001	Location_D
05.01.2017 10:00	ID0001	Location_XYZ
06.01.2017 08:00	ID0001	Location_A
06.01.2017 10:00	ID0001	Location_A
07.01.2017 12:00	ID0001	Location_B
01.01.2017 08:00	ID0002	Location_A
01.01.2017 10:00	ID0002	Location_XYZ
...	....	....
08.01.2017	ID0002	Location_A

and I need to get something like this

DateTime Device ID CurrentLocation Distinct Trip No. Duration in Location

01.01.2017 06:00	ID0001	Location_XYZ
01.01.2017 08:00	ID0001	Location_A		52 hours
02.01.2017 10:00	ID0001	Location_A		52 hours
03.01.2017 12:00	ID0001	Location_C	Trip 1	24 hours
04.01.2017 12:00	ID0001	Location_D	Trip 1	22 hours
05.01.2017 10:00	ID0001	Location_XYZ	Trip 1	22 hours
06.01.2017 08:00	ID0001	Location_A		28 hours
06.01.2017 10:00	ID0001	Location_A		28 hours
07.01.2017 12:00	ID0001	Location_B	Trip 2
01.01.2017 08:00	ID0002	Location_A		2 hours
01.01.2017 10:00	ID0002	Location_XYZ	Trip 3
...	....	....
08.01.2017	ID0002	Location_A

Don't know if this is possible...looking forward to your answers.

best regards

[Zubair_Muhammad]

@masterl1983

I hope there is a better way.

But this works works with your sample data

Duration =
VAR myID = [ Device ID]
VAR myDT = [DateTime]
VAR mylocation = [ CurrentLocation]
VAR mytime =
    CALCULATE (
        MAX ( Table1[DateTime] ),
        FILTER (
            Table1,
            [ Device ID] = myID
                && [DateTime] < myDT
                && [ CurrentLocation] <> mylocation
        )
    )
VAR firstsameinstance =
    COUNTROWS (
        FILTER (
            Table1,
            [ Device ID] = myID
                && [DateTime] > mytime
                && [DateTime] < myDT
        )
    )
VAR nextTime =
    CALCULATE (
        MIN ( Table1[DateTime] ),
        FILTER (
            Table1,
            [ Device ID] = myID
                && [DateTime] > myDT
                && [ CurrentLocation] <> mylocation
        )
    )
VAR firstsameinstancetime =
    MINX (
        TOPN (
            firstsameinstance,
            FILTER ( Table1, [ Device ID] = myID && [DateTime] < myDT ),
            [DateTime], DESC
        ),
        [DateTime]
    )
VAR my_time =
    IF ( firstsameinstance > 0, firstsameinstancetime, [DateTime] )
VAR firstlocation =
    MINX (
        TOPN ( 1, FILTER ( Table1, [ Device ID] = myID ), [DateTime], ASC ),
        [ CurrentLocation]
    )
VAR firsttime =
    MINX (
        TOPN ( 1, FILTER ( Table1, [ Device ID] = myID ), [DateTime], ASC ),
        [DateTime]
    )
RETURN
    IF (
        firstlocation <> "Location_A"
            && [DateTime] = firsttime,
        BLANK (),
        DATEDIFF ( my_time, nextTime, HOUR )
    )

[Zubair_Muhammad]

@masterl1983

Please see attached file as well

[masterl1983]

This seems to work just as expected. Great! Thanks!

So I only need to get the "Distinct Trips" to be calculated. Any ideas on how to achieve that?

[v-piga-msft]

Hi @masterl1983,

Have you solved your problem?

If you have solved, always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best  Regards,

Cherry

[Zubair_Muhammad]

hi @masterl1983

Sorry I had to go out.

I can add trip # with the help of a supporting column.

Please see file attached

First Add this supporting column

Supporting_Column =
VAR a =
    CONTAINS (
        FILTER (
            Table1,
            [ Device ID] = EARLIER ( [ Device ID] )
                && [DateTime] < EARLIER ( [DateTime] )
        ),
        [ CurrentLocation], "Location_A"
    )
VAR mytime =
    CALCULATE (
        MAX ( [DateTime] ),
        FILTER (
            Table1,
            [ Device ID] = EARLIER ( [ Device ID] )
                && [DateTime] < EARLIER ( [DateTime] )
                && [ CurrentLocation] = "Location_A"
        )
    )
VAR b =
    CALCULATE (
        MIN ( [DateTime] ),
        FILTER (
            Table1,
            [ Device ID] = EARLIER ( [ Device ID] )
                && [DateTime] > mytime
                && [ CurrentLocation] <> "Location_A"
        )
    )
VAR c = [ CurrentLocation] <> "Location_A"
RETURN
    IF ( AND ( a, c ), b + RIGHT ( [ Device ID], 1 ) * 4000 )

Now we can get trip no as

Distinct Trip No. =
IF (
    [Supporting_Column] <> BLANK (),
    "Trip "
        & RANKX (
            FILTER ( table1, Table1[Supporting_Column] <> BLANK () ),
            [Supporting_Column],
            ,
            ASC,
            DENSE
        )
)