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.

tamerj1's avatar
tamerj1
Community Champion
4 years ago

Hi rsimpson318 
You did not reply to my question. Not sure if you are still interested in the solution. Anyway, please refer to the sample file with the updated solution https://we.tl/t-so64yQQdvX

I started with grouping latitude and longitude in one column for the ease of calculation

 

Location = Routes[Latitude] & " : " & Routes[Longitude]

 

As the location keeps changing despite the vehicle is considered not moving, then the location need to be updated to accommodate with our our moving/stopping criteria 

 

Adjusted Location = 
IF ( 
    Routes[Distance] <= 0.06,
    VAR PreviousTable = FILTER ( Routes, Routes[Date/Time] < EARLIER ( Routes[Date/Time] ) )
    VAR PreviousStopTable = FILTER ( PreviousTable, Routes[Distance] <= 0.06 )
    VAR PreviousMoveTable = FILTER ( PreviousTable, Routes[Distance] > 0.06 )
    VAR EarliestStopTime = MINX ( PreviousStopTable, Routes[Date/Time] )
    VAR EarliestStopLocation = MINX ( FILTER ( PreviousStopTable, Routes[Date/Time] = EarliestStopTime ), Routes[Location] )
    VAR EarlierStopTime = MAXX ( PreviousStopTable, Routes[Date/Time] )
    VAR EarlierStopLocation = MAXX ( FILTER ( PreviousStopTable, Routes[Date/Time] = EarlierStopTime ), Routes[Location] )
    VAR EarlierMoveTime = MAXX ( PreviousMoveTable, Routes[Date/Time] )
    VAR CurrentStopTime = MAXX ( FILTER ( PreviousMoveTable, Routes[Date/Time] < EarlierMoveTime ), Routes[Date/Time] )
    VAR CurrentStopLocation = MAXX ( FILTER ( PreviousMoveTable, Routes[Date/Time] = CurrentStopTime ), Routes[Location] )
    RETURN
        COALESCE ( IF ( ISBLANK ( EarlierMoveTime ), EarliestStopLocation, CurrentStopLocation ), Routes[Location] )
)

 

Start and end times for each stop are calculated

 

Start Time = 
VAR CurrentDistance =
    Routes[Distance]
VAR LocationTable =
    CALCULATETABLE ( Routes, ALLEXCEPT ( Routes, Routes[Adjusted Location] ) )
RETURN
IF ( 
    CurrentDistance <= 0.06 && COUNTROWS ( LocationTable ) > 1,
    MINX ( 
        LocationTable,
        Routes[Date/Time] 
    )
)
End Time = 
VAR CurrentDistance =
    Routes[Distance]
VAR LocationTable =
    CALCULATETABLE ( Routes, ALLEXCEPT ( Routes, Routes[Adjusted Location] ) )
RETURN
IF ( 
    CurrentDistance <= 0.06 && COUNTROWS ( LocationTable ) > 1,
    MAXX ( 
        LocationTable,
        Routes[Date/Time] 
    )
)

 

Then the stops 

 

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
    )

 

 Finally the measur 

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

rsimpson318's avatar
rsimpson318
Frequent Visitor
4 years ago

Good afternoon tamerj1 .

 

When I try to create the column for Adjusted Location, it gives an error that I've ran out of memory. Did you run into this when you were working on it?

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Morning rsimpson318 

    on sample I had no error. But this a heavy calculation and this is expected for big data. Do you have table for each vehicle or all in one table?

    • rsimpson318's avatar
      rsimpson318
      Frequent Visitor
      4 years ago

      tamerj1 When I ran it, I had approximately 37,000 rows of data. I trimmed that down to one vehicle for one day which was about 1,100 and it did run. It still didn't look like it was calculating correctly but I hadn't had a chance to look into it further.

       

      Once complete, I plan to have all the data in a single table, capturing a month at a time. I expect to have around 600,000 rows.