Forum Discussion

lionx's avatar
lionx
Helper I
3 years ago

Fix errors for flatten BOM Parent-Child Hierarchy using PATH function

I'm trying to visualize the Bill of Materials dataset in power BI. However, when I use the PATH function to flatten the BOM Parent-Child Hierarchy, it comes up with the error as follows.

Any solutions for me? I have tried to solve by following some below links but have no luck. 

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

Bill of Materials (BOM) solution in Excel and PowerBI – The BIccountant

Solved: Parents - Child Hierarchy for Bill of Materials - Microsoft Power BI Community

 

Thanks a lot for your time and assistance.

 

 

 

Need Help

3 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    3 years ago

    All it's saying is that you need to add the values in the parentid column into the 'no' column (even if they just point at themselves).

     

    That will make the error go away.

    • lionx's avatar
      lionx
      Helper I
      3 years ago

      Thanks for your suggestion. I did try that and it works for this specific item. But I do not want to change the data structure as it has thousands of records. Any other solutions? 

  • lionx's avatar
    lionx
    Helper I
    3 years ago

    I have tried to fix the issue using the DAX. First I detect the missing assemblies as follows:

     

    Missing assemblies =
    CALCULATETABLE(
    SUMMARIZE(
    'Item',
    'item'[no],
    'item'[description],
    'item'[Inventory],
    "Assembly id", BLANK(),
    "Quantity per", 1
    ),
    FILTER( 'item', NOT( 'item'[no] IN ALL( 'bomComponent'[no] )))
    )

     

    Then, I create the Hierachical assemblies table that includes all the missing values of 'parentItemN'o in the the 'no' column: 

     

    Hierachical assemblies =
    UNION(
    SELECTCOLUMNS(
    'bomComponent',
    "Assembly id", 'bomComponent'[parentItemNo],
    "Component description", 'bomComponent'[description],
    "Component id", 'bomComponent'[no],
    "Quantity per", 'bomComponent'[quantityPer]
    ),
    SELECTCOLUMNS(
    'Missing assemblies',
    "Assembly id", 'Missing assemblies'[Assembly id],
    "Component description", 'Missing assemblies'[description],
    "Component id", 'Missing assemblies'[no],
    "Quantity per", 'Missing assemblies'[Quantity per]
    )
    )

     

    However, It come up with another issue as captured below:

     

     

    Does my DAX code is correct? I see the topic below is the same but has not resolved yet. 

    Path() Error - Microsoft Power BI Community