Forum Discussion

L_K_'s avatar
L_K_
Helper I
2 years ago
Solved

Calculated column using DAX - filldown

Hello,

I need some help please,

I have a table like this:

ID_NUMNUMBERTEXTLEVEL
10AAA1
20AAA1
100BBB2
200BBA2
1000CCC3
2000CCC3
1001DDD3
2001DDA3

 

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

My ideal look of the table would be like this:

ID_NUMNUMBERTEXTLEVELHIER_1HIER_2HIER_3
10AAA10 / AAA  
20AAA10 / AAA  
100BBB20 / AAA00 / BBB 
200BBA20 / AAA00 / BBA 
1000CCC30 / AAA00 / BBB000 / CCC
2000CCC30 / AAA00 / BBA000 / CCC
1001DDD30 / AAA00 / BBB000 / DDD
2001DDA30 / AAA00 / BBA000 / 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

  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

5 Replies

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

  • Anonymous's avatar
    Anonymous
    Not applicable

    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_'s avatar
      L_K_
      Helper I

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

      • Anonymous's avatar
        Anonymous
        Not applicable

        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.