locked
BOM in PowerPivot RRS feed

  • Question

  • Hello,

    Today I got an interesting question about BOM. Some colleagues wants to know what (raw) products are used in other products. But, these products can be used in other products. So every product can be anywhere in the hierarchies of other products. 

    A child can be used in multiple parents.

    They want to know what product is used in which final product or half product. 

    Hennie


    Tuesday, March 8, 2016 11:05 AM

Answers

  • I don't know how your source table looks like but I suppose you have some parent-child table holding all possible parent-child combinations.

    you can use DAX PATH functions to determine the path from the current product to full product (the top of the tree) and create a hierarchy.  you will however have to foresee a predefined number of levels that is large enough to represent the entire tree in your data.

    So, yes, you can create a drillable hierarchy for a BOM but with a fixed number of levels in that hierarchy. (I have done this in a PowerPivot model)

    Friday, March 18, 2016 6:04 PM
  • hello Hennie (I'm from Flanders Belgium by the way),

    Yes, you flatten out the data.  Where you do this (in PowerPivot or in the source database) depends on your exact requirements.

    If you want to drill to the 'full product' from any node in the tree p.e., you'll keep all your records and you can create your levels as calculated columns in PowerPivot making use of the PATH functions in DAX.

    If you only want the 'full product' to 'raw product' (or vice versa) hierarchies and not the 'half product' to 'raw product' or 'half product' to 'full product' hierarchies, you can also do it in PowerPivot but maybe it is better to do it in p.e. SQL server because you will load less records to powerpivot.

    hope this helps,

    Dries

    Monday, March 21, 2016 2:22 PM

All replies

  • Hi Hennie,

    It's hard for us to understand your requirement, please provide us more information, so that we can make further analysis.

    Regards,


    Charlie Liao
    TechNet Community Support

    Wednesday, March 9, 2016 6:48 AM
  • Hi,

    Here i have a diagram.


    I wonder whether it's possible to create a some kind of hierarchy in PowerPivot to drill down and up in the BOM. 

    Some characteristics:

    • A raw product can be used anywhere in the tree.
    • A child item is not unique to one parent (you can have multiple parents).
    • A parent can have different types of childs (Raw, half).
    • The hierarchy is dynamic, meaning the number of levels is undetermined and unknown upfront. 

    I hope to make this more clear?!

    Wednesday, March 9, 2016 7:15 AM
  • I don't know how your source table looks like but I suppose you have some parent-child table holding all possible parent-child combinations.

    you can use DAX PATH functions to determine the path from the current product to full product (the top of the tree) and create a hierarchy.  you will however have to foresee a predefined number of levels that is large enough to represent the entire tree in your data.

    So, yes, you can create a drillable hierarchy for a BOM but with a fixed number of levels in that hierarchy. (I have done this in a PowerPivot model)

    Friday, March 18, 2016 6:04 PM
  • So, I have to prepare the table for PowerPivot by flattening the data  into  level1, level2, etc and then suppress the empty values?
    Monday, March 21, 2016 11:58 AM
  • hello Hennie (I'm from Flanders Belgium by the way),

    Yes, you flatten out the data.  Where you do this (in PowerPivot or in the source database) depends on your exact requirements.

    If you want to drill to the 'full product' from any node in the tree p.e., you'll keep all your records and you can create your levels as calculated columns in PowerPivot making use of the PATH functions in DAX.

    If you only want the 'full product' to 'raw product' (or vice versa) hierarchies and not the 'half product' to 'raw product' or 'half product' to 'full product' hierarchies, you can also do it in PowerPivot but maybe it is better to do it in p.e. SQL server because you will load less records to powerpivot.

    hope this helps,

    Dries

    Monday, March 21, 2016 2:22 PM
  • Hi there,

    I have the same problem, but the PATH-function doesn't work for me, as my children also have multiple parents (like Hennie's example). So for typical organisational data, the PATH is fine, but for the typical BOM, where one component is used in multiple products, it doesn't work.

    For my understanding, this seems to be intended behaviour: https://msdn.microsoft.com/en-us/library/gg492167.aspx : "... If ID_columnName has duplicates and parent_columnName is the same for those duplicates then PATH() returns the common parent_columnName value; however, if parent_columnName value is different for those duplicates then PATH() returns an error. In other words, if you have two listings for the same employee ID and they have the same manager ID, the PATH function returns the ID for that manager. However, if there are two identical employee IDs that have different manager IDs, the PATH function returns an error. ..."

    So I'm wondering if anyone has a solution to this in PowerPivot/DAX

    (BTW: I've created a solution in M/PowerQuery, but that's pretty slow for large datasets, so I'm looking for a DAX-solution that returns the PATH-data. Also came across a nice DAX-solution for single-level BOM: http://powerpivotforum.com.au/viewtopic.php?t=358, but that doesn't seem to work for multilevel)

    Thx for your help!


    Imke Feldmann
    MVP Data Platform
    TheBIccountant.com


    Monday, May 8, 2017 8:59 AM
    Answerer
  • Managed to speed up the M-solution considerably so that it can now handle large datasets: http://www.thebiccountant.com/2017/05/08/dynamic-bill-of-material-bom-solution-in-excel-and-powerbi/

    This is also a workaround for the DAX PATH-function to handle multiple parents.


    Imke Feldmann
    MVP Data Platform
    TheBIccountant.com

    Monday, May 8, 2017 8:56 PM
    Answerer