Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

GPS Segmenting

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 com...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Anonymous ,

    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.

v-yanjiang-msft's avatar
v-yanjiang-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi Anonymous ,

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.