Forum Discussion
Return all components in a bom structure
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 |
3 Replies
- barritown
Solution Sage
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
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.
