Calculated column using DAX - filldown

[L_K_]

Hello,

I need some help please,

I have a table like this:

ID_NUM	NUMBER	TEXT	LEVEL
1	0	AAA	1
2	0	AAA	1
1	00	BBB	2
2	00	BBA	2
1	000	CCC	3
2	000	CCC	3
1	001	DDD	3
2	001	DDA	3

Now I need to make a hierarchy with the numbers and text.

My ideal look of the table would be like this:

ID_NUM	NUMBER	TEXT	LEVEL	HIER_1	HIER_2	HIER_3
1	0	AAA	1	0 / AAA
2	0	AAA	1	0 / AAA
1	00	BBB	2	0 / AAA	00 / BBB
2	00	BBA	2	0 / AAA	00 / BBA
1	000	CCC	3	0 / AAA	00 / BBB	000 / CCC
2	000	CCC	3	0 / AAA	00 / BBA	000 / CCC
1	001	DDD	3	0 / AAA	00 / BBB	000 / DDD
2	001	DDA	3	0 / AAA	00 / BBA	000 / DDA

The first level works fine with:

var number = LEFT(TABLE[NUMBER],1)

var text = LOOKUPVALUE(TABLE[TEXT],TABLE[NUMBER],number)

return number &" / "& text

Then I tried something similar for HIAR_2 but it returned an error.

A table of multiple values was supplied where a single value was expected.

Can someone please help me figure this out?

Kind regards,

L

[v-binbinyu-msft]

Hi @L_K_ ,

Sorry, so far, to my knowledge, this may not be achieveable by DAX.

Because DAX can't be calculated in a round-robin fashion.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[L_K_]

May I ask how I could achieve this in another way? If you have a suggestion?
would really appreciate any help.

[v-binbinyu-msft · Accepted solution]

Hi @L_K_ ,

Please try to create M code to achieve it.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[L_K_]

Thank you, it was quick and easy to do in power query.

Got help with this video: https://www.youtube.com/watch?v=MK1jwHz8hoc

[L_K_]

sorry, there is a mistake... the last two rows for HIER_3 should be 001 / DDD and 001 / DDA.