GPS Segmenting

[Astargle95]

Hello. I am importing a DB2 table into power bi with vehicle gps information. What I am trying to do is segment out the gps points. I would like to create a column in Power Bi so that everytime 0 comes up in the miles per hour column, it creates a new segment so then I can add that column to a slicer and see the routes for starting and stopping. But if there are multiple 0's in a row like segment 1, I would need those to be in the same segment until the vehicle moves and the next 0 would start the following segment. In the photo, for segment 1, I would like all of those records to have a "1" value in the segment column and so on. Not every day starts at 0mph either. For the example below, the first record of that day is 2.48mph. I would like to just ignore those first three records and start my segmenting at the first 0mph. Thanks

[v-yanjiang-msft · Accepted solution]

Hi @Astargle95 ,

According to your description, here's my solution.

Create three calculated columns.

0_Date =
MAXX (
    FILTER (
        'Table',
        'Table'[Date] <= EARLIER ( 'Table'[Date] )
            && 'Table'[Rate] = 0
    ),
    'Table'[Date]
)

0_DateNew =
IF (
    COUNTROWS ( FILTER ( 'Table', 'Table'[0_Date] = EARLIER ( 'Table'[0_Date] ) ) ) = 1,
    MINX (
        FILTER (
            'Table',
            'Table'[Date] > EARLIER ( 'Table'[Date] )
                && COUNTROWS ( FILTER ( 'Table', 'Table'[0_Date] = EARLIER ( 'Table'[0_Date] ) ) ) <> 1
        ),
        'Table'[Date]
    ),
    'Table'[0_Date]
)

Segment =
VAR _Rank =
    IF (
        'Table'[Date]
            >= MINX ( FILTER ( 'Table', 'Table'[Rate] = 0 ), 'Table'[Date] ),
        "Segment" & " "
            & RANKX (
                'Table',
                IF (
                    MAXX (
                        FILTER (
                            'Table',
                            'Table'[Date] <= EARLIER ( 'Table'[Date] )
                                && 'Table'[Rate] = 0
                        ),
                        'Table'[Date]
                    )
                        = BLANK (),
                    MAXX ( 'Table', 'Table'[Date] ),
                    [0_DateNew]
                ),
                ,
                ASC,
                DENSE
            ),
        BLANK ()
    )
RETURN
    _Rank

Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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