Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Create a multi level item list

Hi, i have the following situation?

 

In our company we work with a ''Bill of Material''. This is a list of items you need to create a new item. Als you can see in the screenshot below this is how this table works in my query. The item on the left is the main item and the item on the right are the items that you need to create the item on the left. In many cases the item on the right also has a list of items to make that item. Youcan have this up to maybe like 8 levels in our company. 

 

My question is: How can i create a query that when i select the main item it shows al the different items that are needed to create that and it has checked al the next levels? So basically show all the items that are needed to create the items before them.

Please can someone help me. I have already tried merging it with the same table for like 11 times and then creating a pivot table with it, but the load is too heavy then.

 

Need Help
Show and Tell
Tips and Tricks

2 Replies

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    Create a Calculated Table:

    AllItems = 
    VAR MaxLevels = 8
    RETURN
    GENERATE(
    'YourTable',
    VAR CurrentItem = 'YourTable'[Item]
    RETURN
    SUMMARIZE(
    FILTER(
    'YourTable',
    'YourTable'[Item] = CurrentItem || 
    CONTAINS(
    SUMMARIZE('YourTable', 'YourTable'[Item]),
    'YourTable'[SecondItem], 
    BLANK()
    )
    ),
    'YourTable'[SecondItem]
    )
    )

    Create Measures for Each Level

    Level1Items = 
    CALCULATE(
    DISTINCTCOUNT('YourTable'[SecondItem]),
    FILTER('YourTable', 'YourTable'[Item] = "Your Main Item Here") // Replace with the actual main item or use a selected value
    )

    Level2Items =
    CALCULATE(
    DISTINCTCOUNT('YourTable'[SecondItem]),
    FILTER('YourTable', 'YourTable'[SecondItem] IN VALUES(Level1Items))
    )

    -- Continue similarly for Level3, Level4, ..., up to Level8

    TotalItemsNeeded = 
    SUMX(
    { [Level1Items], [Level2Items], [Level3Items], /* Add up to Level8 */ },
    [Value]
    )

    Create a Slicer: Allow users to select the main item.
    Use a Table/Matrix Visual: You can display your results using a Matrix visual to show the hierarchy and item dependencies.

     

    If this helped, a Kudos 👍 or Solution mark would be great! 🎉
    Cheers,
    Kedar
    Connect on LinkedIn

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous ,

    It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered? If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
    Looking forward to your reply!