Hi everybody,
Our business relies on a distance*weight measure for all vehicle trips as a sort of "work done" metric. Currently we have an SQL database which contains all trip data except the distances. That's done in Excel.
Vehicles follow fixed paths through a network of nodes to which we add more nodes fairly regularly (as the footprint of operations grows). To calculate distances between one point and another we setup, many years ago, a relatively light Excel with a VBA implementation of Dijkstra's algorithm which computes distances from every node to every other node (using nodes and edges). To generate our distance*weight value, we pull daily reports of voyages from the database, importantly: (departure)(destination)(weight) and then in another excel fill in the distances by consulting the Excel page (from A to Q you look at row A column Q - or inverse as the matrix is symmetrical).
What I was hoping to do was use a DAX expression to pull distance automatically from the Excel file, but I think I've hit a dead end and I have a feeling the systems are a square peg and a round hole. It's my first time using DAX but not my first time consulting a function reference.
1) consult the node table to ensure destination and departure nodes are programmed in
1.1) Easy enough.
2) Use lookupvalue to grab the value from the distance matrix
2.1) I'm not sure how I would select a column in the distance table by using a variable pulled from the database as the column name. Columns will be added regularly I think it would need to be a dynamic reference..
2.2) Given the symmetric nature of the table and the the restriction placed on "search_value" with LOOKUPVALUE (A scalar expression that does not refer to any column in the same table being searched.) I'm not sure I could even use the function.
Does anybody have any suggestions on how I might accomplish this, if it is possible - or how else I might structure the data to make it so it is?
