DAX/POWER BI Add Grandchild and Greatgrandchild Columns within a single Parent Child Table

1
votes

I have a table that contains a list of part numbers and their child PN's. The problem is that some of the child part numbers have grand child part numbers. An example would be an assembly part number that has a sub assembly with its own child parts.

This Table illustrates more clearly what it is I am trying to accomplish.

enter image description here

So far I have tried using the lookupvalue feature in a new column but am having a difficult time pointing to the child of the partno I am looking up. The logic I am using is lookup the value of the SubPartNo for the current record in the PartNo field and return the SubPartNo for the matching record.

If MS PowerBI has a better more efficient way to make this happen I am open to that as well! Thanks for the help!

1
lookuppowerbidax
There are several ways to mimic LOOKUPVALUE functionality. It depends on the needs you have. Is it a recursive hierarchy? What is your expected result? Add more details in order to help you. - alejandro zuleta
Yes it is a recursive hierarchy. In the end I am trying to get a total cost of all purchased components and subcomponents for our assemblies. That said I need to be able to break out costs associated with purchased or manufactured Part Numbers. For example if I were to try and determine what the total cost of purchased materials is for an assembly our system will add in the manufacturing costs of the subassembly where really what I need is the cost of the material used in the subassembly. - Sutniko
Without a basic sample of your model there is no much we can do. Edit your question to include test data and expected result. - alejandro zuleta
The linked table is an example of the table. Is not showing? - Sutniko
Yes, it is showing but it is still unclear to me what you are after. In your image I see a table at the left what I assume is your current table and at the right side it seems to the table you want to produce. Could you elaborate your question? - alejandro zuleta

1 Answers

0
votes

See http://www.daxpatterns.com/parent-child-hierarchies/

P.S. - This is the answer. I do not have to personally write anything.