Forum Discussion
Calculated column using DAX - filldown
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
- Anonymous2 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
- L_K_Helper I
sorry, there is a mistake... the last two rows for HIER_3 should be 001 / DDD and 001 / DDA.
- AnonymousNot 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_Helper I
May I ask how I could achieve this in another way? If you have a suggestion?
would really appreciate any help.- AnonymousNot 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.