How to get Lowest Level Hierarchy

[Anil2264]

Hi Team,

I need one help,Hope you can help me out.

I want to get the Lowest Level data to be reflect in my reporting, The lowest level is till 19th Level.

Not able to crack this requirement. I am also providing sample data below.

Key	Parent Node	Path	Output
1		1	1
2	1	1|2	2
2	3	1|2|3	3
2	4	1|2|3|4	4
5		5	5
6	5	5|6	6
7	5	5|7	7
8	5	5|8	8
9		9	9
10	9	9|10	10
11	10	9|10|11	11
12		12	12
13	12	12|13	13

@amitchandak please help it's bit urgent

Thanks & regards

Anil

[Anil2264]

Hi @v-easonf-msft 

Thanks for you response i would like to go in more deep in heirarchy level. Please refer to the below table and would like to have a lowest level component in front on Material. Can you please help me out how to get it

I only need to report extreme lowest level component in my table.

Key	Parent Node	Material	Component
1		AB
2	1	AB	BC
3	1	AB	CD
4	1	AB	DE
5	1	AB	CD
6	2	BC	EF
7	1	AB	FG
8	1	AB	GH
9	1	AB	IJ
10	9	IJ	KL
11	1	AB	LM
12	1	AB	MN
13	1	AB	NO
14	1	AB	PQ
15	1	AB	RS
16	1	AB	ST
17	1	AB	UV

[v-easonf-msft]

Hi, @Anil2264 

Not fully sure what you want. There are obvious differences between the two sample data you provided.

Can you share me the column 'path' in your second sample data?

I think it's difficult to get the result of 'Component' based on column 'Material' due to the lack of data .

Take the third row of records as an example, due to the lack of parent node= key= '3'，it is hard to confirm that the lowest component = 'CD'

Best Regards,
Community Support Team _ Eason

[v-easonf-msft]

Hi, @Anil2264 

You can also try calculated columns as below:

length = PATHLENGTH('Table'[Path])

Last_item = PATHITEM('Table'[Path],'Table'[length])

Best Regards,
Community Support Team _ Eason

[Anil2264]

Hi @v-easonf-msft 

Thanks for you response i would like to go in more deep in heirarchy level. Please refer to the below table and would like to have a lowest level component in front on Material. Can you please help me out how to get it

Key	Parent Node	Material	Component
1		AB
2	1	AB	BC
3	1	AB	CD
4	1	AB	DE
5	1	AB	CD
6	2	BC	EF
7	1	AB	FG
8	1	AB	GH
9	1	AB	IJ
10	9	IJ	KL
11	1	AB	LM
12	1	AB	MN
13	1	AB	NO
14	1	AB	PQ
15	1	AB	RS
16	1	AB	ST
17	1	AB	UV

[rsbin]

@Anil2264,

I have a thought...might not be the cleanest, but you said it was urgent.

Start with the RIGHT function. 

Output = RIGHT( [NodePath], 2 )

But since your last characters can be a single digit or two digit, do something with an IF or SWITCH and Containsstring to see if it contains your delimiter "|".  If it does, do Right([NodePath], 1] instead.

You may then need to wrap it in a VALUE statement to convert it back to a number.

Personally, I would need to do it in two or 3 steps, but if you do it correctly, it can be done in one statement.

Hope this at least gives you an idea where to start.