Forum Discussion
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 ...
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