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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Shannon_Leong
Helper I
Helper I

Automate Hierarchy Slicer

Hi Community, is there any other approach to make hierarchy slicer automated? Right now i am using Path() formula in Powerbi, after this formula, I created five columns because my hierarchy has five levels. But if I were to put in a new file with new codes and more levels, my slicer would not work anymore. So if there some sort of function I can create so that my slicer can automatically adapt to the data? 

1 ACCEPTED SOLUTION

@Shannon_Leong  - there's a parameter in the function for 'max steps' = you need to set that to 5, or whatever max number you expect to have. For putting it into the slicer, you'd need to load all the columns into the slicer in preparation for having data in there. Not ideal, but you've posed a tricky question. 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

6 REPLIES 6
ChandanaMB13
Regular Visitor

Hi having a similar requirement, could you please share the solution

AllisonKennedy
Super User
Super User

@Shannon_Leong What's your data source look like? Sounds like it's in the format that works well with SANKEY visual, but not hierarchical slicer. https://excelwithallison.blogspot.com/2021/07/custom-visual-review-sankey.html

 

For the hierarchy you need to create those as separate columns, so must know how many levels you have / want. (if i understood your question correctly)

 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Hi @AllisonKennedy thank you for your reply. This is how my data looks like currently. But I wish to automate this solution such that if i added in more data with more hierarchy levels, the slicer will auto adapt to it and create more columns. Currently I have 5 hierarchy levels. There are only three displayed below... 

EntityCodeParentCodePathReversePath1ReversePath2ReversePath3
1001100110011001  
100210011001|100210021001 
100310011001|100310031001 
100410011001|100410041001 
100510011001|100510051001 
100610031001|1003|1006100610031001
100710031001|1003|1007100710031001
100810041001|1004|1008100810041001
100910041001|1004|1009100910041001

@Shannon_Leong It's really difficult to do this without knowing how many columns there will be. 

 

This blog is helpful; 

https://www.thebiccountant.com/2021/02/10/guest-post-using-list-accumulate-for-input-output-genealog... 

 

Output 3 is the only thing that could accomodate any number of levels in your hierarchy, but I'm not sure how to get it into a hierarchical slicer from that format.... 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Hi @AllisonKennedy Thank you for your reply. I managed to invoke the function. However, my hierarchy has 5 levels. The function is showing that there are two levels only. Do you know how i can solve this. Furthermore, i'm not sure how i can integrate this solution into my hierarchy slicer 

@Shannon_Leong  - there's a parameter in the function for 'max steps' = you need to set that to 5, or whatever max number you expect to have. For putting it into the slicer, you'd need to load all the columns into the slicer in preparation for having data in there. Not ideal, but you've posed a tricky question. 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.