Automate Hierarchy Slicer

[Shannon_Leong]

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? 

[ChandanaMB13]

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

[AllisonKennedy]

@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)

[Shannon_Leong]

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... 

EntityCode	ParentCode	Path	ReversePath1	ReversePath2	ReversePath3
1001	1001	1001	1001
1002	1001	1001|1002	1002	1001
1003	1001	1001|1003	1003	1001
1004	1001	1001|1004	1004	1001
1005	1001	1001|1005	1005	1001
1006	1003	1001|1003|1006	1006	1003	1001
1007	1003	1001|1003|1007	1007	1003	1001
1008	1004	1001|1004|1008	1008	1004	1001
1009	1004	1001|1004|1009	1009	1004	1001

[AllisonKennedy]

@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.... 

[Shannon_Leong]

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 

[AllisonKennedy · 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.