Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
akhilduvvuru
Helper III
Helper III

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

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!

1 ACCEPTED SOLUTION

HI @akhilduvvuru 

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

onurbmiguel__1-1681919589369.png

 

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!

 


Best regards


Bruno Costa | Super User


 


Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 


Take a look at the blog: PBI Portugal 


 


View solution in original post

6 REPLIES 6
DOLEARY85
Super User
Super User

Hi, 

 

can you post a sample of the data?

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

Level1Level2Level3Level4Level5Amounts
A  A1AA113
A  A2AA21234
A  A3AA31245
BB1B3A4AA4565
BB1B4A5AA557
BB1B5A5AA63456
BB2B6A5AA72345
BB2B7A8AA8124
C  A9AA9546


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

Level1Level4Level5Amounts
A  13
 A1AA11234
 A2AA21245
 A3AA3 


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

Level1Level2Level3Level4Level5Amounts
BB1B3A4AA4565
BB1B4A5AA557
BB1B5A5AA63456
BB2B6A5AA72345
BB2B7A8AA8124


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

Level1Level4Level5Amount
CA9AA9546
akhilduvvuru
Helper III
Helper III

hi @akhilduvvuru 

Please take a look at my suggestion: 

 

onurbmiguel__0-1681769587772.png

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!

 

 

 


Best regards


Bruno Costa | Super User


 


Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 


Take a look at the blog: PBI Portugal 


 


Thanks @onurbmiguel_ This really works. 

 

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

HI @akhilduvvuru 

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

onurbmiguel__1-1681919589369.png

 

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!

 


Best regards


Bruno Costa | Super User


 


Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 


Take a look at the blog: PBI Portugal 


 


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.