Parents - Child Hierarchy for Bill of Materials

[wsindharta]

Hi i have two question, appreciate if you can help me.

a. I'm trying to create a visualization for Bill of Materials in power BI, however there are few child that have multiple parents, so it prevent me creating path using DAX function. i attached the screen shot of error.

 

b. i ignore those part number with multiple parents, first and this is the 2nd problem, on visualization, especially for level 3 onward. there's blank value, which is from level 1 and level 2. below is the table and visualization. is there anyway to solve this ?

blank here basically level 2 part number.

[wsindharta · Accepted solution]

All,

thanks for reply, unfortunately to change the structure will be very tedious job, as in actual scope the level of hieararchy can be as long as 12 level, and the multiple child happen at different level.

i found one link that solve the problem

https://www.thebiccountant.com/2017/02/14/dynamically-flatten-parent-child-hierarchies-in-dax-and-po...

[ChrisMendoza]

@wsindharta,

PATH ( ) [ID} should be unique. something more like:

ID	Parent
A
B	A
C	A
D	A
E	A
F	A
G	A
B1	B
B2	B
B3	B
C1	C

I have used https://www.daxpatterns.com/parent-child-hierarchies/ several times in my own projects as a resource. Maybe it will work for you if you can change your data structure.

[v-jiascu-msft]

Hi @wsindharta,

Could you please mark the proper answers as solutions?

Best Regards,
Dale

[wsindharta]

@ChrisMendoza

i've been trying to follow step by step instruction from the link, but can't get it right. and the sample there didn't cover multiple parents child hierarchy...

[v-jiascu-msft]

Hi @wsindharta,

So what's the result that you expected? Since there are multiple values in the path, we can't use the function PATH here. Please refer to dax/path-function-dax.

Best Regards,

Dale