Recursive bom

Question

I have two tables BOM and BOM-details.&nbsp;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&nbsp; &nbsp; &nbsp; Assembly&nbsp; &nbsp; Part/Assembly1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10.132&nbsp; &nbsp; &nbsp; &nbsp; 10.0372&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10.037&nbsp; &nbsp; &nbsp; &nbsp;11.7852&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10.037&nbsp; &nbsp; &nbsp; &nbsp;11.786In this example i would like to see that 11.785 is used in 10.037 (directly) and 10.132 (indirectly).&nbsp;&nbsp;

v-xicai · Answer

Hi Oomsen&nbsp;,
&nbsp;
You may create path column using DAX below.
&nbsp;
Path = PATH(Table1[Part/Assembly],Table1[Assembly])
&nbsp;
You can learn more: https://radacad.com/parsing-organizational-hierarchy-or-chart-of-accounts-in-power-bi-with-parent-child-functions-in-dax.
&nbsp;
Best Regards,
Amy
&nbsp;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
&nbsp;

oomsen · Answer

v-xicai&nbsp;Thanks i can make it happen with this dax.&nbsp;But, there are some parentitems which aren't in the child column. Any suggestion how to fix that?

v-xicai · Answer

Hi Oomsen&nbsp;,
&nbsp;
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.
&nbsp;
Best Regards,Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
&nbsp;

oomsen · Answer

v-xicai&nbsp;i continued trying. The Path dax is clear for me and will work if my data is correct.&nbsp;That's the issue now. I cannot create complete child and parent tables.My current data is based on bill of materials.&nbsp;BomID&nbsp; &nbsp; &nbsp; Parent&nbsp; &nbsp; &nbsp; Child&nbsp;Bom1&nbsp; &nbsp; &nbsp; &nbsp; 10.002&nbsp; &nbsp; &nbsp; 10.213Bom1&nbsp; &nbsp; &nbsp; &nbsp; 10.002&nbsp; &nbsp; &nbsp; 12.046Bom1&nbsp; &nbsp; &nbsp; &nbsp; 10.002&nbsp; &nbsp; &nbsp; 10.026Bom2&nbsp; &nbsp; &nbsp; &nbsp; 10.026&nbsp; &nbsp; &nbsp; 10.970Bom3&nbsp; &nbsp; &nbsp; &nbsp; 10.213&nbsp; &nbsp; &nbsp; 10.971&nbsp; &nbsp; &nbsp;The problem is that not all parents are in the column of childs and vice versa.&nbsp;

Forum Discussion

Recursive bom

4 Replies

Related content

Recursion Strategy

BOM tree

BOM Explosion Logic Creation

Expandable BOM Tree from flat BOM report

BOM explosion inside BOM