Forum Discussion
Calculate Route Stop Based on Distance/Duration
- 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.
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?
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.