Forum Discussion

masterl1983's avatar
masterl1983
Frequent Visitor
7 years ago

Identify rows belonging together based on field value changes

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 trave...
Need Help
masterl1983's avatar
masterl1983
Frequent Visitor
7 years ago

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?

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

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

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    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