Forum Discussion
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?
Anonymous - 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.
6 Replies
- AllisonKennedy
Community Champion
Anonymous 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)
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
Anonymous It's really difficult to do this without knowing how many columns there will be.
This blog is helpful;
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....
Hi having a similar requirement, could you please share the solution
