Counting parts in multilevel BOM

Anonymous — Thu, 11 May 2023 15:48:36 GMT

Hi All,

after many months of reading in this forum, this is my first post.

I hope I learned much and am able to write it clearly enough to find a good solution.

I have two lists. One bill of material which contains parent-child relations. And one list which marks the product I want to see.

For every product in the second list, I want to know how many parts X it contains. The problem is that Product 'A' can, for example, contain 2 items 'B'. And one item 'B' contains 3 items 'X'.

I tried to summarize it with PRODUCTX() but it seems I messed something up with the filter contexts.

I made a minimal example.

I expected this:

But get this:

Here is my DAX-Code:

DEFINE VAR billOffMaterials = SELECTCOLUMNS ( { ( "A", 2, "B" ), ( "B", 3, "X" ), ( "C", 4, "X" ) }, "Parent", [Value1], "Quantity", [Value2], "Child", [Value3] ) VAR markedProducts = { ( "A" ), ( "C" ) } VAR joinedTable = GENERATE ( markedProducts, FILTER ( billOffMaterials, FALSE () || [Parent] = [Value] || [Parent] IN SELECTCOLUMNS ( FILTER ( billOffMaterials, [Value] = [Parent] ), "Child", [Child] ) ) ) EVALUATE joinedTable ORDER BY [Value] EVALUATE SUMMARIZE ( joinedTable, [Value], "Quatity", PRODUCTX ( joinedTable, [Quantity] ) )

Best regards,

Peter

Re: Counting parts in multilevel BOM

lbendlin — Sun, 14 May 2023 00:11:46 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

topic Re: Counting parts in multilevel BOM in DAX Commands and Tips

Counting parts in multilevel BOM

Re: Counting parts in multilevel BOM