Forum Discussion
Multilevel Slicer
Hi there,
I am looking to use a multi level slicer for a project forecasting report. I want the report to be able to filter both by year (top hierarchy) and by spend type (project milestones). The slicer would essentially look something like this:
ALL
-2020
- PM
- Design
- Supervision
-2021
- PM
- Design
- Supervision
-2022
- PM
- Design
- Supervision
The source data is shown by project (row), and the columns are shown as:
2020 PM, 2020 Design, 2020 Supervision, 2021 PM, 2021 Design, 2021 Supervision, 2022 PM... and so on.
The values are based on if statements from other parts of the worksheets with outputs text "Yes" or "No".
I hope this makes sense and someone is able to help with a solution please!
Thank you,
Laura
Hi laura8685646
In addition to Fowmy‘s reply, you may need to use split function after you unpivot the columns.
Due to I don't know your data model, I build a sample table.
Unpivot all columns and then split Attribute column by space.
Finally, you can build a hierachy slicer by Attribute 1 column and Attribute 2 column.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
You must evict the column headers in Rows in Power Query to configure it correctly.
Check out these videos on unpivot options: https://www.youtube.com/channel/UCKwBEguA8IlBubIobaOormg
________________________
If my answer was helpful, click OK as the solution to help other members find it useful
Click the Thumbs-Up icon if you like this answer 🙂
You can use the Slicer Visual in Power BI Desktop to achieve it. You can include your fields in the fields selection and it will form a hierarchy, further, Show ALL option can be turned on as well.
Please check the example below
________________________
If my answer was helpful, please click Accept it as the solution to help other members find it useful
Click on the Thumbs-Up icon if you like this reply 🙂
Hi Fowmy,
I think if the data had been set up differently, this would be an appropriate answer.
The challenge I am finding is the values are set up for Y/N, where the fields which I am more needing are the column headers. There must be a work around rather than reconfiguring a full data set and its sources. So the question is can you still do this hierarachical slicer if the fields are column headers rather than values?
Thanks,Laura
Hi laura8685646
In addition to Fowmy‘s reply, you may need to use split function after you unpivot the columns.
Due to I don't know your data model, I build a sample table.
Unpivot all columns and then split Attribute column by space.
Finally, you can build a hierachy slicer by Attribute 1 column and Attribute 2 column.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi laura8685646
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.
Best Regards,
Rico Zhou
