Using PATH function on large tables

[TBerger]

I want to load a large parent-child hierarchy table and parse it using the PATH function in calculated columns.

The table contains about 2.5M rows that belongs to about 115,000 hierarchies.

I started on PBI Desktop with few samples and it works fine.

After I publish the report to a PBIRS and check it I changed the SQL server view to allow loading the full table.

I scheduled a refresh and after 2 hours I received a time out error.

I presume that the when I use the full table the PATH is looking on all the rows to find the parent and it took long time.

I can distinguish the rows that belong to a hierarchy because there is a tree ID column for each row.
Can I iterate through the tree ID and let PATH working just on the relevant rows each time?
Another solution will be to parse the hierarchy just when a user choose one.

I didn't find the way to do ither of this.

Maybe there is another way to solve it?

Thank you.

[MFelix]

Hi @TBerger ,

Try to do it on power query:

https://www.excelnaccess.com/replicating-path-function-of-dax-in-power-query-m/