Return all components in a bom structure

[willtunechi]

I'm trying to create a table in BI where I search for a specific item and it returns all the components of that item and the components of the components (which can be several items), below is an example of the table and what I need. Thank you

Item	component
123	777
123	987
123	546
123	444
234	987
234	481
234	678
234	111
345	333
345	222
777	321
777	600
444	450
444	451
444	452

result finding item 123

Item	component
123	777
123	987
123	546
123	444
123	321
123	600
123	450
123	451
123	452

[barritown]

Hi @willtunechi,

If you have only one level in your hierarchy and need to perform this operation only for "123", you can create a new table like this:

In plain text:

New Table = 
VAR _tbl = UNION ( SELECTCOLUMNS ( FILTER ( Data, [Item] = 123 ), "Component", [Component] ), 
                   { 123 } )
RETURN CROSSJOIN ( SELECTCOLUMNS ( { 123 }, "Item", [Value] ), 
                   SELECTCOLUMNS ( FILTER ( Data, [Item] IN _tbl ), "Component", [Component] ) )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[willtunechi]

Thanks for the answer, there will be more than 10 levels

[barritown]

Missed that point, sorry. 

I'd use PATH then, but it doesn't accept multiple parents as in your case.