cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

Can I use DAX to lookup [row,column] from an attached excel of distances between nodes in a graph?

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?

1 ACCEPTED SOLUTION

I decided to bounce the idea off of a colleague from another office, and we found what I think is a good solution. I wanted to leave it here for posterity. Nothing worse than finding your exact problem on the internet just to see the solution was never presented (or found).

 

Basically, the symmetrical matrix setup is not very conducive to DAX. My colleague suggested we look into melting (reshaping) the output of the algorithm, that is, to systematically convert it into a much more row intensive 3 column set up with DEPARTURE-DESTINATION-DISTANCE as the format. We'll been implementing that instead of following this dead end rabbit hole.

 

Here are a couple references:

https://stats.idre.ucla.edu/stata/modules/reshaping-data-wide-to-long/

https://www.listendata.com/2015/02/excel-formula-convert-data-from-wide-to.html

 

 

View solution in original post

3 REPLIES 3
Super User IV
Super User IV

Sounds very interesting. Would love to work on this, can you post some sample data (text)? Seems like it might have elements of Transitive Closure. https://community.powerbi.com/t5/Quick-Measures-Gallery/Transitive-Closure/m-p/783828#M388


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi Greg, I had never heard of Transitive Closure before. Thanks for the heads up. After a bit of research it sounds like it applies to a similar case but where distance between nodes is irrelevant. I suppose it could be possible to use it to generate a path between two nodes and then take each edge connection along that path and sum their values for the full distance. I'll have to take a little longer a look at it.

 

If you're interested, I can't unfortunately give actual data but here is an idea of what we're looking at

 

database:

2020-05-06 | place_1 | place_5 | 15-tonnes

2020-05-06 place_2 | place_5 | 60-tonnes

2020-05-06 place_4 | place_3 | 15-tonnes

2020-05-04 place_1 | place_5 | 15-tonnes

2020-05-04 place_2 | place_5 | 15-tonnes

2020-05-05 place_4 | place_6 | 15-tonnes

 

Excel, nodes tab:

place_1, place_2, place_3, place_4, place_5, place_6

 

Excel, edges tab: 

place_1 | place_3 | 400m

place_2 | place_3 | 500m

place_3 | place_4 | 40m

place_3 | place_5 | 600m

place_4 | place_6 | 1000m

 

Excel, distances tab:

X           place_1 | place_2 | place_3 | place_4 | place_5 place_6 |

place_1 |     0           900         400    ....

place_2 |     900        0            500 ...

place_3 |  etc.

place_4 |  etc.

place_5 |  etc.

place_6 |  etc.

 

Thank you very much,

 

Byz

I decided to bounce the idea off of a colleague from another office, and we found what I think is a good solution. I wanted to leave it here for posterity. Nothing worse than finding your exact problem on the internet just to see the solution was never presented (or found).

 

Basically, the symmetrical matrix setup is not very conducive to DAX. My colleague suggested we look into melting (reshaping) the output of the algorithm, that is, to systematically convert it into a much more row intensive 3 column set up with DEPARTURE-DESTINATION-DISTANCE as the format. We'll been implementing that instead of following this dead end rabbit hole.

 

Here are a couple references:

https://stats.idre.ucla.edu/stata/modules/reshaping-data-wide-to-long/

https://www.listendata.com/2015/02/excel-formula-convert-data-from-wide-to.html

 

 

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors