Forum Discussion
Creating a BOM Drill Down
Hi All,
I am fairly new to PowerBI, and what I'm trying to do is create a tool in BI that allows me to filter by an item number, and have it return all BOM's that the ingredient goes into. Sounds simple enough, but what happens is each step of the manufacturing process the item number then changes.
I'm trying to use the PATH function, but because ingredients can go into multiple formulas/BOMS I'm getting errors that there are multiple values. I can't think of anything other than using PATH, but when a "child" has multiple "parents" it doesn't seem to be working out as planned....
Here are two sample BOMs of the table that I'm trying to get into Power BI.
| Org Code | Formula Vers | Formula No | Formula Description | Formula Quantity | Formula Uom | Ingredient Line No | Ingredient No |
| FRW | 3 | 123-456 | SIROL, BULK | 1 | ML | 1 | 290-826 |
| FRW | 3 | 123-456 | SIROL, BULK | 1 | ML | 2 | 290-536 |
| FRW | 3 | 123-456 | SIROL, BULK | 1 | ML | 3 | 301-685 |
| FRW | 3 | 123-456 | SIROL, BULK | 1 | ML | 4 | 290-610 |
| FRW | 3 | 123-456 | SIROL, BULK | 1 | ML | 5 | 270-030 |
| FRW | 3 | 123-456 | SIROL, BULK | 1 | ML | 6 | 103-367 |
| FRW | 4 | 987-654 | TACR BULK | 1 | ML | 1 | 290-826 |
| FRW | 4 | 987-654 | TACR BULK | 1 | ML | 2 | 300-660 |
| FRW | 4 | 987-654 | TACR BULK | 1 | ML | 3 | 301-685 |
| FRW | 4 | 987-654 | TACR BULK | 1 | ML | 4 | 300-435 |
| FRW | 4 | 987-654 | TACR BULK | 1 | ML | 5 | 270-030 |
| FRW | 4 | 987-654 | TACR BULK | 1 | ML | 6 | 103-367 |
2 Replies
- Greg_Deckler
Community Champion
SoColdOutside So what's the expected output or grouping out of that set of sample data?
Hi SoColdOutside ,
I created some data:
Here are the steps you can follow：
1. Create calculated column.
Flag = CONCATENATEX(FILTER(ALL('Table'), 'Table'[child]=EARLIER('Table'[child])),[parents],",")2. Result:
If it does not meet your expectations, can you express the expected results in the form of pictures, we can better help you.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
