Forum Discussion

tjgagner's avatar
tjgagner
Frequent Visitor
1 year ago
Solved

Generate Recursive Bill of Material

I have a table imported into PowerBI from that has the relation between a top-level Bill-of-Material part number "BOM Material" and child part numbers "Component Material".  The challenge I am having is that child "Component" part numbers themselves might be "BOM materials" I would like to see a flattened version of the Bill of Material with all component part numbers and the different levels represented.

 

The approach I am thinking about taking is populating a New Table with a query from the original, which includes recursion to get to a flat BOM like shown below.

 

How can I write a query that does this type of recursion?

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for lbendlin's concern about this issue.

     

    Hi, tjgagner 

    I am glad to help you.

     

    Perhaps you can refer to my DAX to create a calculation table:

     

    FlattenedBOM =
UNION (
    SELECTCOLUMNS (
        FILTER (
            'OriginalTable',
            'OriginalTable'[BOM Material]
                IN SELECTCOLUMNS ( 'OriginalTable', 'OriginalTable'[Component Material] )
                    = FALSE ()
        ),
        "BOM Material", [BOM Material],
        "Component Material", [Component Material],
        "Level", 1
    ),
    ADDCOLUMNS (
        SELECTCOLUMNS (
            FILTER (
                CROSSJOIN (
                    'OriginalTable',
                    SELECTCOLUMNS (
                        'OriginalTable',
                        "BOM Material2", 'OriginalTable'[BOM Material],
                        "Component Material2", 'OriginalTable'[Component Material]
                    )
                ),
                [BOM Material2] = [Component Material]
            ),
            [BOM Material],
            "Component Material", [Component Material2]
        ),
        "Level", 2
    )
)

     

     

    Result:

     


    I have attached the pbix for this example below, I hope it helps!

     

     

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
    Best Regards,
    Fen Ling,
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks for lbendlin's concern about this issue.

     

    Hi, tjgagner 

    I am glad to help you.

     

    Perhaps you can refer to my DAX to create a calculation table:

     

    FlattenedBOM =
UNION (
    SELECTCOLUMNS (
        FILTER (
            'OriginalTable',
            'OriginalTable'[BOM Material]
                IN SELECTCOLUMNS ( 'OriginalTable', 'OriginalTable'[Component Material] )
                    = FALSE ()
        ),
        "BOM Material", [BOM Material],
        "Component Material", [Component Material],
        "Level", 1
    ),
    ADDCOLUMNS (
        SELECTCOLUMNS (
            FILTER (
                CROSSJOIN (
                    'OriginalTable',
                    SELECTCOLUMNS (
                        'OriginalTable',
                        "BOM Material2", 'OriginalTable'[BOM Material],
                        "Component Material2", 'OriginalTable'[Component Material]
                    )
                ),
                [BOM Material2] = [Component Material]
            ),
            [BOM Material],
            "Component Material", [Component Material2]
        ),
        "Level", 2
    )
)

     

     

    Result:

     


    I have attached the pbix for this example below, I hope it helps!

     

     

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
    Best Regards,
    Fen Ling,
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • tjgagner's avatar
      tjgagner
      Frequent Visitor
      1 year ago

      Thank you for your time answering my question. I did try and use this query but hung up on the calculation step ("working on it"). I will come back to it but am going to try the lbendlin referenced solution as well.