Forum Discussion

joshua1990's avatar
joshua1990
Post Prodigy
6 years ago

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:

ItemUnitAttr1Attr2Attr3Attr4
A1     
A2     

 

Then I have one BOM like:

Parent ItemChild ItemValueUnit
A1A2XX
A1A3XX
A2A4XX

 

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?

 

Need Help

2 Replies

  • edhans's avatar
    edhans
    Community Champion
    6 years ago

    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's avatar
    kentyler
    Solution Sage
    6 years ago

    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.