Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Post Prodigy
Post Prodigy

Multi Level BOM with one Item Master

Hello everybody!

I need to include a multi-level BOM into my data model.

I have one item master that contains all attributes to every item:



Then I have one BOM like:

Parent ItemChild ItemValueUnit


As you can see the item master has all parent and child items.

Now, how would you link that BOM ith the item master?

I would assume I need a relation to Parent Item AND to Child Item - but just one can be active.

Does someone have any suggestions?


Super User
Super User

Hi @joshua1990 

You have at least two choices here.

  1. You can use the PATH() function in DAX which will let you work through Parent/Child heirarchies. There is an article on it at SQLBI.
  2. Alternatively, you could model this in Power Query to unpivot the item columns, turning this:
    1. edhans_0-1594504017088.png


    2. into this:
    3. edhans_1-1594504075134.png


The new table in Power Query would now allow you to have your Item DIM table filter your item number here as a 1 to many relationship and then you would just use something like this to get specific parent/child items:

Parent Totals = 
        'Table'[Item Attribute] = "Parent Item"


The full M code transformation is below, but basically I just selected your Parent/Child columns, right-clicked, and selected UNPIVOT COLUMNS.

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lFyNAISIIalpVKsDkzQGEiAJCwsIIIgtqMJkABJmJsrxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Parent Item" = _t, #"Child Item" = _t, Value = _t, Unit = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parent Item", type text}, {"Child Item", type text}, {"Value", Int64.Type}, {"Unit", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Value", "Unit"}, "Item Atribute", "Item Number"),
    #"Reordered Columns" = Table.ReorderColumns(#"Unpivoted Columns",{"Item Atribute", "Item Number", "Value", "Unit"})
    #"Reordered Columns"

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Solution Sage
Solution Sage

DAX does not support hierarchies natively. You can do things like BOMs though, you have to create some calculated columns to make them work. Here is the pattern for that problem, which includes code examples and a step by step explanation.

Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!

Join the conversation at We Talk BI find out more about me at Slow BI

Helpful resources


Fabric certifications survey

Certification feedback opportunity for the community.


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.