Multi Level BOM with one Item Master

[joshua1990]

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:

Item	Unit	Attr1	Attr2	Attr3	Attr4
A1
A2

Then I have one BOM like:

Parent Item	Child Item	Value	Unit
A1	A2	X	X
A1	A3	X	X
A2	A4	X	X

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?

[edhans]

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.

[kentyler]

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.