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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
joshua1990
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:

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?

 

2 REPLIES 2
edhans
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 = 
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.

 



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
kentyler
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

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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