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.
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?
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?
- rsimpson3184 years agoFrequent Visitor
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.