NETWORK.PATH OpenBI
The shortest path, in hops, between two nodes.
Group: Networks. OpenBI’s own: DAX has no function of this name.
Syntax
NETWORK.PATH ( <links>, <from>, <to>, <source>, <target> [, <directed>] )| Parameter | Type | Description |
|---|---|---|
links | Table | The rows that make the network, one link a row: a table of the model or a table expression, as the filters leave it. |
from | Expression evaluated for each row | One end of each row's link, such as a payer, a customer or a place. |
to | Expression evaluated for each row | The other end of each row's link. |
source | Value | The node the path starts at. |
target | Value | The node the path ends at. |
directed | Boolean optional | TRUE when each link runs one way, from its first end to its second. FALSE or left out, a link works both ways. |
Returns
Table. A row a step of the path, with the columns Step (1 the source) and Node.
Remarks
- The path is a shortest one in hops: the fewest links. When several are as short, one of them is given, the same one whatever the order of the rows.
- No weight is taken: every link is one hop.
- When the target can't be reached, or either end given is no node of the network, the result is an empty table. From a node to itself, it is one row.
- Directed, the path follows each link only from its first end to its second.
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
From Ann to Gus
EVALUATE
VAR Links =
DATATABLE (
"From", STRING, "To", STRING,
{
{ "Ann", "Bo" }, { "Ann", "Cy" }, { "Bo", "Cy" }, { "Cy", "Di" },
{ "Di", "Ed" }, { "Ed", "Fay" }, { "Di", "Fay" }, { "Fay", "Gus" }
}
)
RETURN
NETWORK.PATH ( Links, [From], [To], "Ann", "Gus" )| Step | Node |
|---|---|
| 1 | Ann |
| 2 | Cy |
| 3 | Di |
| 4 | Fay |
| 5 | Gus |
Across town in short hops
From Skøyen to Kampen using only roads of six minutes or less.
EVALUATE
NETWORK.PATH (
FILTER ( Distances, Distances[Minutes] <= 6 ),
Distances[From], Distances[To],
"Shop Skøyen", "Shop Kampen"
)| Step | Node |
|---|---|
| 1 | Shop Skøyen |
| 2 | Shop Frogner |
| 3 | Shop Majorstuen |
| 4 | Shop St. Hanshaugen |
| 5 | Shop Grünerløkka |
| 6 | Shop Tøyen |
| 7 | Shop Kampen |
No way back
F receives money but pays nobody, so following the payments there is no path from F to A.
EVALUATE
VAR Payments =
DATATABLE (
"Payer", STRING, "Payee", STRING, "Amount", INTEGER,
{
{ "A", "B", 100 }, { "B", "C", 80 }, { "C", "A", 60 }, { "C", "D", 50 },
{ "D", "E", 40 }, { "E", "C", 30 }, { "E", "F", 20 }
}
)
RETURN
NETWORK.PATH ( Payments, [Payer], [Payee], "F", "A", TRUE )| Step | Node |
|---|
Related functions
NETWORK.REACH, NETWORK.FLOW, NETWORK.NODES.
Open Graph, OpenBI's graph library, does the work: get_shortest_path_dijkstra. See the OAX network functions and Open Graph.