Recursive bom

[Oomsen]

I have two tables BOM and BOM-details. 

BOM includes assemblies and BOM-details includes parts and sub-assemblies.

I want to get insight in which BOMS a part is used when i change the part.

Currently i can only look one level up or down.

I would like to see all the levels starting from the lowest level.

Level      Assembly    Part/Assembly

1             10.132        10.037

2             10.037       11.785

2              10.037       11.786

In this example i would like to see that 11.785 is used in 10.037 (directly) and 10.132 (indirectly).

[v-xicai]

Hi @Oomsen ,

You may create path column using DAX below.

Path = PATH(Table1[Part/Assembly],Table1[Assembly])

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

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Oomsen]

@v-xicai 

Thanks i can make it happen with this dax. 

But, there are some parentitems which aren't in the child column. Any suggestion how to fix that?

[v-xicai]

Hi @Oomsen ,

If the parentitems aren't in the child column, then they may not form hierarchy. Or could you please share your sample data and desired output screenshots for further analysis? Do mask sensitive data before uploading.

Best Regards,
Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Oomsen]

@v-xicai i continued trying. The Path dax is clear for me and will work if my data is correct. 

That's the issue now. I cannot create complete child and parent tables.

My current data is based on bill of materials. 

BomID      Parent      Child 

Bom1        10.002      10.213

Bom1        10.002      12.046

Bom1        10.002      10.026

Bom2        10.026      10.970

Bom3        10.213      10.971     

The problem is that not all parents are in the column of childs and vice versa.