Dynamically change the hierarchy levels (between 2 to 5) based on level 1 value

[akhilduvvuru]

Hi Team, Have a requirement as to change the levels (2-5) based on Level 1.

I have 5 fields (Level1, Level2.... Level5).

1. If I have value "A" in Level1, then my matrix table in Power BI should not show Level2 and Level3 when users expand. It should directly show Level4 and then Level5 

2. If I have value "B" in Level1, then my matrix table in Power BI should show Level2, Level3, Level 4 and Level5

3. 1. If I have value "c" in Level1, then my matrix table in Power BI should only show Level2, Level3 and Level5 when users expand. It should not show Level4.

Not sure if this can be done in Power BI. If Yes, please help me with the same.

Thanks!

[DOLEARY85]

Hi, 

can you post a sample of the data?

[akhilduvvuru]

@DOLEARY85 - Thanks for your response.
Here is the input

Level1	Level2	Level3	Level4	Level5	Amounts
A			A1	AA1	13
A			A2	AA2	1234
A			A3	AA3	1245
B	B1	B3	A4	AA4	565
B	B1	B4	A5	AA5	57
B	B1	B5	A5	AA6	3456
B	B2	B6	A5	AA7	2345
B	B2	B7	A8	AA8	124
C			A9	AA9	546

All level fields will be in "Rows" in matrix visual
If I expand "A" in level 1, my expected output is skip 2 and 3

Level1	Level4	Level5	Amounts
A			13
	A1	AA1	1234
	A2	AA2	1245
	A3	AA3

If I expand "B" in level 1, my expected output is all levels

Level1	Level2	Level3	Level4	Level5	Amounts
B	B1	B3	A4	AA4	565
B	B1	B4	A5	AA5	57
B	B1	B5	A5	AA6	3456
B	B2	B6	A5	AA7	2345
B	B2	B7	A8	AA8	124

If I expand "C" in level 1, my expected output is skip Level 2 and 3

Level1	Level4	Level5	Amount
C	A9	AA9	546

[akhilduvvuru]

@amitchandak @Ahmedx @Greg_Deckler @Ashish_Mathur @onurbmiguel_ @DOLEARY85 @PhilipTreacy @ppm1 - Any help is much appriciated. Thanks!

[onurbmiguel_]

hi @akhilduvvuru 

Please take a look at my suggestion: 

here is the file with the example, please analyse the power query. 

levels.pbix

Best regards

Bruno Costa | Impactful Individual

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍

You can also check out BI4ALL's website and our data solutions!

[akhilduvvuru]

Thanks @onurbmiguel_ This really works. 

However, I need the solution in DAX. Mine is direct query

[onurbmiguel_ · Accepted solution]

HI @akhilduvvuru 

Hi just update the file with a calculated table , please try in the previous link 

Table DQ = 
FILTER(
    UNION(
        SELECTCOLUMNS(Table_base,"Level1",Table_base[Level1],"Amounts",Table_base[Amounts],"Level",MID(NAMEOF(Table_base[Level2]),14,6),"Value",Table_base[Level2]),
        SELECTCOLUMNS(Table_base,"Level1",Table_base[Level1],"Amounts",Table_base[Amounts],"Level",MID(NAMEOF(Table_base[Level3]),14,6),"Value",Table_base[Level3]),
        SELECTCOLUMNS(Table_base,"Level1",Table_base[Level1],"Amounts",Table_base[Amounts],"Level",MID(NAMEOF(Table_base[Level4]),14,6),"Value",Table_base[Level4]),
        SELECTCOLUMNS(Table_base,"Level1",Table_base[Level1],"Amounts",Table_base[Amounts],"Level",MID(NAMEOF(Table_base[Level5]),14,6),"Value",Table_base[Level5])
    ),
[Value]<>BLANK()
)

Best regards

Bruno Costa | Power Participant

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍


You can also check out BI4ALL's website and our data solutions!