Forum Discussion

rsimpson318's avatar
rsimpson318
Frequent Visitor
4 years ago
Solved

Calculate Route Stop Based on Distance/Duration

Hello,   I'm trying to figure out a DAX or calculated column to determine a route stop based on the duration and distance traveled (or not). I have a table with a list of points and the time each p...
  • tamerj1's avatar
    tamerj1
    4 years ago

    rsimpson318 
    Hello agian
    I had some free time to do work on it. Here is a sample file folr your reference https://we.tl/t-WjszOERm4s
    My proposed solution involves adding calculated columns:

    Start Time = 
    VAR CurrentDistance =
        Routes[Distance]
    VAR LocationTable =
        CALCULATETABLE ( Routes, ALLEXCEPT ( Routes, Routes[Latitude], Routes[Longitude] ) )
    RETURN
    IF ( 
        CurrentDistance = 0 && COUNTROWS ( LocationTable ) > 1,
        MINX ( 
            LocationTable,
            Routes[Date/Time] 
        )
    )
    End Time = 
    VAR CurrentDistance =
        Routes[Distance]
    VAR LocationTable =
        CALCULATETABLE ( Routes, ALLEXCEPT ( Routes, Routes[Latitude], Routes[Longitude] ) )
    RETURN
    IF ( 
        CurrentDistance = 0 && COUNTROWS ( LocationTable ) > 1,
        MAXX ( 
            LocationTable,
            Routes[Date/Time] 
        )
    )
    Stop = 
    VAR StopNumber =
        RANKX ( FILTER ( Routes, NOT ISBLANK ( Routes[Start Time] ) ), Routes[Start Time],, ASC, Dense )
    RETURN
        IF ( 
            NOT ISBLANK ( Routes[Start Time] ),
            "Stop - " & StopNumber
        )

    Then the measure would be

    Stop Duration = 
    VAR Duration =  
        DATEDIFF ( MAX ( Routes[Start Time] ), MAX ( Routes[End Time] ), SECOND )
    RETURN
        QUOTIENT ( Duration, 60 ) & "Min and " & MOD ( Duration, 60 ) & " Sec"

    One last step would be to filter the table in order to remove the blank row

    The report looks like this

    I hope this satisfies your requirement.