Search for parent value

[Alphasyrius]

Hello All,

I have a table called "Project", the unique key is a project number on 6 digits, column name "ProjectNumber". Each project can be part of a larger project, so I have a column "ParentProject" that can be either empty when there is no parent object, or contain the project number on 6 digits when there is one.

The problem is that a parent project can also have a parent, but the data I receive only include the first parent.

My objective here is to be able to pull out data that shows, for any given project, the complete list of child projects, including level 2, 3 or even 4.

I am a very basic user of PowerBI, I was doing fine up to now, but here, I am stuck. I thought lookupvalue function would do the trick, but I might be doing it wrong. 

Also, ideally, I would have a loop like "Do.. While" (if that is even possible) to find the highest parent of any project, but I could create additioal column also to find the level 1 level 2 and so on.

Last but not least : any recommendation of books for me to learn a bit more advanced functions and about DAX for PowerBI?

Thanks!

[amitchandak]

Refer, if this can help

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

[v-deddai1-msft]

Hi @Alphasyrius ,

According to your description, are you trying  to  Lookupvalue from another table with multiple values? You can create a calculated column for search values from another table. Please refer to https://community.powerbi.com/t5/Desktop/Lookupvalue-from-another-table-with-multiple-values/td-p/67...

Would you please inform us more detailed information( your sample data(by OneDrive for Business)) if possible? Then we will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.

Best Regards,

Dedmon Dai