Multiple parents hierarchy - Find longest path

[armandprr92]

Hello, I have a multiple parents hierarchy and I would like to find the most time consuming path available.

In blue the original table and in orange the expected result.

I have found this function for finding the optimal path, but I've been unable to edit it to my needs. Dijkstra's Algorithm in Power Query

I'm quite lost right now.

Thank you very much for your help.

[Anonymous]

Hi @armandprr92,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you want to find the highest time consuming path in your data hierarchy. As @lbendlin already provided a response to your query, please go through it and accept it as solution if it solves yours problem.

I would also take a moment to thank @lbendlin, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[lbendlin]

Here's a more contemporary implementation in SQL.  Should be possible to convert that into Power Query.

Dijkstra's Algorithm – SQLServerCentral

[lbendlin]

Here is the PBIX file with your data and the algorithm you mentioned. The algorithm calculates the shortest path, not the longest path.

[lbendlin · Accepted solution]

Here's the PBIX with both shortest and longest path

[armandprr92]

Super thanks! I've done multiple checks and it works as expected.

Thank you very much for the effort.

PS: To give some context, I needed it in order to calculate a gantt with multiple parent hierarchy. In that gantt I have a start time of the first installation but not for the others. Then for the other's start time I needed to calculate the most far away date, that is exacly where it will start that installation. And so on.