ROUTES OpenBI

Delivery rounds that leave a depot, visit the places in view and come back, each vehicle within its capacity, planned with Google OR-Tools.

Group: Operations research. OpenBI’s own: DAX has no function of this name.

Syntax

ROUTES ( <places>, <name>, <latitude>, <longitude>, <vehicles> [, <demand> [, <capacity> [, <depot>
         [, <seconds> [, <distances>, <fromPlace>, <toPlace>, <distance>]]]]] )

Returns

Table. A row for each stop of each vehicle used: Vehicle (numbered from 1), Order (0 at the depot, and the depot again last), Place with its Lat and Lon, Load (the demand served up to and including that stop), Distance (driven by then), and the vehicle's whole round in Route distance and Route load.

Remarks

• It plans a capacitated vehicle routing problem: every place but the depot is visited once, by one vehicle, no vehicle carries more than its capacity, and the total distance is as short as the search finds. Start from the cheapest arcs, then a guided local search for the seconds given.
• Without a table of distances the distance between two places is the great circle between their coordinates, in meters. With one, its pairs are used in the direction given (from the first place to the second), and a pair it lacks falls back on the great circle; Distance and Route distance are then in the table's units, such as minutes.
• The places are the rows of the table as the filters leave them, so a slicer on districts plans the routes for those districts only. Keep the depot in view: a named depot that the filters remove makes the call an error.
• Name the depot. Left out, it is the first place in alphabetical order whose demand is 0, which may not be the one meant: without a demand, every place has 0.
• When the vehicles cannot carry all the demand, there are no routes and the call is an error: add vehicles or capacity. Fewer than two places, a negative distance, or a place with neither coordinates nor distances to the others is an error too.
• A search limited in time may find other routes of the same length, or close to it, in another session; the same question asked again while the engine runs gets the same routes at once.
• A table of distances must come with all four of its arguments; at most 1,000 places.

Compared with DAX

An OpenBI function: DAX has none like it, so a measure or query that uses it works in OpenBI only, not in Power BI or Analysis Services.

Examples

Three vans of 70 crates

The depot at Alnabru and fifteen shops, each van carrying at most 70 of the 178 crates, distances along the great circle.

EVALUATE
SELECTCOLUMNS (
    ROUTES ( Places, Places[Place], Places[Lat], Places[Lon], 3, [Deliveries], 70 ),
    "Van", [Vehicle],
    "Stop", [Order],
    "Place", [Place],
    "Crates so far", [Load],
    "Meters so far", [Distance]
)

On the road: driving meters

The same vans with the Distances table's meters by road: each van's round, and the kilometers in all.

EVALUATE
VAR Rounds =
    ROUTES (
        Places, Places[Place], Places[Lat], Places[Lon], 3, [Deliveries], 70, "Depot Alnabru", 2,
        Distances, Distances[From], Distances[To], Distances[Meters]
    )
RETURN
    ROW (
        "Vans used", COUNTROWS ( FILTER ( Rounds, [Order] = 0 ) ),
        "Crates", SUMX ( FILTER ( Rounds, [Order] = 0 ), [Route load] ),
        "Kilometers", SUMX ( FILTER ( Rounds, [Order] = 0 ), [Route distance] ) / 1000
    )

One van for the eastern shops

The filter keeps the depot and three shops; one van without a capacity takes them in turn.

EVALUATE
SELECTCOLUMNS (
    CALCULATETABLE (
        ROUTES ( Places, Places[Place], Places[Lat], Places[Lon], 1, [Deliveries], , "Depot Alnabru" ),
        Places[District] IN { "Alnabru", "Tøyen", "Kampen", "Vålerenga" }
    ),
    "Stop", [Order],
    "Place", [Place],
    "Crates so far", [Load],
    "Meters so far", [Distance],
    "Round", [Route distance]
)

Related functions

SCHEDULE, OPTIMIZE, CALCULATETABLE, NETWORK.PATH.

Last updated 3 November 2026 · OpenBI Studio 0.3.74