BOM with multi-level parent-child hierarchy

[khufu]

I have browsed through the previous threads and found a couple of leads but haven't been able to solve this. I would basicly need to list all the components and materials it takes to make a sub-assembly. I have two data tables which contain all the relevant information. The first table lists all the sub-assemblies and components. The second table lists all the links between sub-assemblies and components. The relevant columns in the data tables look like follows:

Table 1:

Title

Sub-assembly 1

Sub-assembly 2

Sub-assembly 3

Component A

Component B

Component C

Table 2:

Title                      Sub-component

Sub-assembly 1    Component B

Sub-assembly 2    Sub-assembly 1

Sub-assembly 2    Component C

Sub-assembly 3    Component A

Sub-assembly 3    Component C

So, the sub-assebly 2 is made of Component C and sub-assembly 1 (of which contains the component B). The data represents a tree-like product structure/BOM, with up to 4 or 5 sub-levels. I would need to be able to display the BOM of any particular product, calculate the cost of materials etc.

At first I have tried to duplicate the table2 and create direct relationship from table1 Title --> table2 Title and from table2 Sub-component --> table2(duplicate) Title etc. The latter relationship is many to many, so obviously the relationship can't be activated. Tried to make a new table of the distinct sub-components of the table2 and use it between table2 and table2(duplicate), but I get the "Can't determine relationships between the fields..." error. Relationship between table1 and table2, and table2 and table2(duplicate) seem to work, but the relationship from table1 to table2(duplicate) don't seem to work (or at least I can't show data from table1, table2 and table2(duplicate) simultaneously in single graph without the error). Perhaps the fact that sub-assemplies have different amout of sub-levels is causing this.

I have also considered about processing the data pre Power BI or in DAX, but I'm not sure which way to go. Any thoughts or tips would be greatly appreciated!

Below are some links that I have found that touch the topic:

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

http://www.thebiccountant.com/2017/02/14/dynamically-flatten-parent-child-hierarchies-in-dax-and-pow...

[caruso1058]

Hello @v-yuezhe-msft 

I can't seem to find the blog post you were referring to in your response. Would you be able to share the blog post you referred to in your answer?

[v-yuezhe-msft · Accepted solution]

@khufu,

1. Create a function following the guide in this blog and rename it to fnFlattenPCHierarchy.


2. Add index column in Table 1.


3. Use Merge Queries-> Merge Queries as New to merge Table 1 and Table 2.


4. Expand the column in the merged table as follows.


5. Add a conditional column in the merged table as follows.


6. Rename columns in the merged table.


7. Remove columns in the merged table.


8. Add a new blank query in Power BI Desktop and paste the following code to Advanced Editor of the blank query.

let
    Source = fnFlattenPCHierarchy(Merge2, "NodeKey", "ParentKey", "Title")
in
    Source

Regards,
Lydia

[khufu]

After running the latest Power BI update the previously discussed pbix seems to have broken down. In other words when the refresh is ran the query gets stuck at "Evaluating". All the previous queries seem to refresh just fine, but the blank query that contains the code is the problem.

Any idea how I could fix this?

Edit: Tried to roll back to previous version with no luck. Previously the same data model loaded just fine on the same system, and full refresh took maybe 20-30 seconds. Tried to weak preferences and that somewhat reduced the memory load, but I still can't run the refresh. The system memory is 80-90% in use when Power BI is evaluating.

[mmunoa]

i'm having that same problem... the query gets stuck at "Evaluating" on the last step. Do you remember wich thing have you done to solve it?

Thanks! I tried to remove unnecesary columns, replace nulls... but none of them worked..

[khufu]

Okay, I was able to fix that. Some kind of problem with the data, after cleaning things up and getting rid of useless columns the refresh runs once again.

However, I still have one issue. Our ERP manipulates the product structures and in some cases collapses the first level of the product structure. The data model should be able to handle that. Unfortunately to my understanding M doesn't support path functions. I should somehow figure way to collapse the first level of some product structures, ie make a parent child, find the parent of the parent, make level 3 component a level 2 component etc. Any thoughts?

[v-yuezhe-msft]

@khufu,

Please open a new thread about your issue.

Regards,
Lydia

[khufu]

Thank you Lydia for your reply, and clear and detailed instructions! I will try to implement them as soon as possible (maybe this evening local time), and we'll see how it goes. Thanks again!

[khufu]

I did manage to solve the problem with the instructions above. I had to make a minor change on step 5, because my actual data contains numerous different sub-components, and I didn't want to make an if statement for each and every one of them. I ended up doing another merge with table 1 on step 5, just to get the index number (not sure if this is the most efficient way).

Thanks again for help!