Multi Level BOM with one Item Master

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. 

       

   2. into this:
   3. 

       

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 = 
SUMX(
    FILTER(
        'Table',
        'Table'[Item Attribute] = "Parent Item"
    ),
    'Table'[Value]
)

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

let
    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"})
in
    #"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.