Identify rows belonging together based on field value changes

Question

Hi,&nbsp;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 -&gt; travel to 6 o'clock -&gt; travel back to 12 o'clock.&nbsp;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.&nbsp;I addition I need to get the the time (hours) which indicates how long a Device is within the current location.&nbsp;So my initial table looks like&nbsp;DateTime Device ID CurrentLocation Distinct Trip No. Duration in Location&nbsp;01.01.2017 06:00ID0001Location_XYZ&nbsp;&nbsp;&nbsp;01.01.2017 08:00ID0001Location_A&nbsp;&nbsp;&nbsp;02.01.2017 10:00ID0001Location_A&nbsp;&nbsp;&nbsp;03.01.2017 12:00ID0001Location_C&nbsp;&nbsp;&nbsp;04.01.2017 12:00ID0001Location_D&nbsp;&nbsp;&nbsp;05.01.2017 10:00ID0001Location_XYZ&nbsp;&nbsp;&nbsp;06.01.2017 08:00ID0001Location_A&nbsp;&nbsp;&nbsp;06.01.2017 10:00ID0001Location_A&nbsp;&nbsp;&nbsp;07.01.2017 12:00ID0001Location_B&nbsp;&nbsp;&nbsp;01.01.2017 08:00ID0002&nbsp;Location_A&nbsp;&nbsp;&nbsp;01.01.2017 10:00ID0002&nbsp;Location_XYZ&nbsp;&nbsp;&nbsp;...........&nbsp;&nbsp;08.01.2017ID0002Location_A&nbsp;&nbsp;&nbsp;and I need to get something like this&nbsp;&nbsp;DateTime Device ID CurrentLocation Distinct Trip No. Duration in Location&nbsp;01.01.2017 06:00ID0001Location_XYZ&nbsp;&nbsp;&nbsp;01.01.2017 08:00ID0001Location_A&nbsp;&nbsp;52 hours&nbsp;02.01.2017 10:00ID0001Location_A&nbsp;&nbsp;52 hours&nbsp;03.01.2017 12:00ID0001Location_CTrip 1&nbsp;24 hours&nbsp;04.01.2017 12:00ID0001Location_DTrip 1&nbsp;22 hours&nbsp;05.01.2017 10:00ID0001Location_XYZTrip 1&nbsp;22 hours&nbsp;06.01.2017 08:00ID0001Location_A&nbsp;&nbsp;28 hours&nbsp;06.01.2017 10:00ID0001Location_A&nbsp;&nbsp;28 hours&nbsp;07.01.2017 12:00ID0001Location_B&nbsp;Trip 2&nbsp;&nbsp;01.01.2017 08:00ID0002&nbsp;Location_A&nbsp;&nbsp;2 hours&nbsp;01.01.2017 10:00ID0002&nbsp;Location_XYZ&nbsp;Trip 3&nbsp;&nbsp;...........&nbsp;&nbsp;08.01.2017ID0002Location_A&nbsp;&nbsp;&nbsp;Don't know if this is possible...looking forward to your answers.&nbsp;best regards

zubair_muhammad · Answer

masterl1983
&nbsp;
I hope there is a better way.
But this works works with your sample data
&nbsp;
Duration =
VAR myID = [ Device ID]
VAR myDT = [DateTime]
VAR mylocation = [ CurrentLocation]
VAR mytime =
    CALCULATE (
        MAX ( Table1[DateTime] ),
        FILTER (
            Table1,
            [ Device ID] = myID
                &amp;&amp; [DateTime] &lt; myDT
                &amp;&amp; [ CurrentLocation] &lt;&gt; mylocation
        )
    )
VAR firstsameinstance =
    COUNTROWS (
        FILTER (
            Table1,
            [ Device ID] = myID
                &amp;&amp; [DateTime] &gt; mytime
                &amp;&amp; [DateTime] &lt; myDT
        )
    )
VAR nextTime =
    CALCULATE (
        MIN ( Table1[DateTime] ),
        FILTER (
            Table1,
            [ Device ID] = myID
                &amp;&amp; [DateTime] &gt; myDT
                &amp;&amp; [ CurrentLocation] &lt;&gt; mylocation
        )
    )
VAR firstsameinstancetime =
    MINX (
        TOPN (
            firstsameinstance,
            FILTER ( Table1, [ Device ID] = myID &amp;&amp; [DateTime] &lt; myDT ),
            [DateTime], DESC
        ),
        [DateTime]
    )
VAR my_time =
    IF ( firstsameinstance &gt; 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 &lt;&gt; "Location_A"
            &amp;&amp; [DateTime] = firsttime,
        BLANK (),
        DATEDIFF ( my_time, nextTime, HOUR )
    )

&nbsp;

zubair_muhammad · Answer

masterl1983
&nbsp;
Please see attached file as well
&nbsp;

masterl1983 · Answer

This seems to work just as expected. Great! Thanks!So I only need to get the "Distinct Trips" to be calculated. Any ideas&nbsp;on how to achieve that?

zubair_muhammad · Answer

hi masterl1983
&nbsp;
Sorry I had to go out.
&nbsp;
I can add trip # with the help of a supporting column.
Please see file attached
&nbsp;
First Add this supporting column
&nbsp;
Supporting_Column =
VAR a =
    CONTAINS (
        FILTER (
            Table1,
            [ Device ID] = EARLIER ( [ Device ID] )
                &amp;&amp; [DateTime] &lt; EARLIER ( [DateTime] )
        ),
        [ CurrentLocation], "Location_A"
    )
VAR mytime =
    CALCULATE (
        MAX ( [DateTime] ),
        FILTER (
            Table1,
            [ Device ID] = EARLIER ( [ Device ID] )
                &amp;&amp; [DateTime] &lt; EARLIER ( [DateTime] )
                &amp;&amp; [ CurrentLocation] = "Location_A"
        )
    )
VAR b =
    CALCULATE (
        MIN ( [DateTime] ),
        FILTER (
            Table1,
            [ Device ID] = EARLIER ( [ Device ID] )
                &amp;&amp; [DateTime] &gt; mytime
                &amp;&amp; [ CurrentLocation] &lt;&gt; "Location_A"
        )
    )
VAR c = [ CurrentLocation] &lt;&gt; "Location_A"
RETURN
    IF ( AND ( a, c ), b + RIGHT ( [ Device ID], 1 ) * 4000 )

Now we can get trip no as
&nbsp;
Distinct Trip No. =
IF (
    [Supporting_Column] &lt;&gt; BLANK (),
    "Trip "
        &amp; RANKX (
            FILTER ( table1, Table1[Supporting_Column] &lt;&gt; BLANK () ),
            [Supporting_Column],
            ,
            ASC,
            DENSE
        )
)

v-piga-msft · Answer

Hi&nbsp;masterl1983,
&nbsp;
Have you solved your problem?
&nbsp;
If you have solved,&nbsp;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.
&nbsp;
If you still need help, please feel free to ask.
&nbsp;
Best&nbsp; Regards,
Cherry

Forum Discussion

Identify rows belonging together based on field value changes

5 Replies

Related content

Identifying duplicates

Rolling Date Identifiers

Identifying Stock On Hand

Using Import Models and Dataflows Together

Performance identifying Measure

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