Counting parts in multilevel BOM

xalPeter · ‎05-11-2023

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