Automate a hierarchy with increasing levels

[Anonymous]

Hello,

I have some issues with automating a tree hierarchy. I don’t know the final level of the hierarchy, so I need it fully automated. Given a set of data:

I found a lot of solutions where I can manually get the hierarchy in a column schema and use it with a DAX expression to prepare and use the date like this:

But I have now 2 open struggles: First of all I have currently 23 levels(!) and they will continue to grow. So this should be fully automated without the need of knowing the amount of levels before. And I need to display measures for every ID. Which leads me to my main questions:

1. Preparing the hierarchy to a drilldown capable format. I get the data from a SQL CTE, should I get it from there in a column schema? Or is there a viable solution in PowerBI to get It done automatically?
2. If a new level rises, and I managed to get the preparation automated, I need to integrate the new column (level) automatically in the drilldown hierarchy for a matrix. How can I manage this?

Thank you in advance!

[amitchandak]

@Anonymous , Have you already tried path , pathitem etc

https://radacad.com/parsing-organizational-hierarchy-or-chart-of-accounts-in-power-bi-with-parent-child-functions-in-dax