Forum Discussion
Power BI DAX Filter Matrix/Table Based on Slicer Hierarchy Level
I've a parent / child slicer and would like to apply an extra filter when a child value is selected. I want to use the native slicer and build a hierachy with Business as parent and Product as child.
| Business | Product | Relation | Team | Filter Column |
| HR | Hr1 | Dedicated | A | Include |
| HR | Hr1 | Generic | B | Exclude |
| Marketing | Mkt1 | Dedicated | C | Include |
| Marketing | Mkt1 | Generic | D | Exclude |
| Marketing | Mkt2 | Generic | E | Exclude |
| Marketing | Mkt2 | Dedicated | F | Include |
When a parent is selected in the hierachy, eg. "Marketing", I'd like to show all 4 teams C, D, E & F. However, when a child hierachy value is selected, eg. Mkt1, then I want to exclude the Generic relation rows in the table or matrix. The reason is Generic teams support entire Business and should be shown only at business level, where as dedicated teams are only for specific products.
Expected output when Marketing is selected at parent level
| Business | Product | Relation | Team |
| Marketing | Mkt1 | Dedicated | C |
| Marketing | Mkt1 | Generic | D |
| Marketing | Mkt2 | Generic | E |
| Marketing | Mkt2 | Dedicated | F |
Expected output when Mkt1 is selected under Marketing (should exclude Gneric Relation Team)
| Business | Product | Relation | Team |
| Marketing | Mkt1 | Dedicated | C |
Is this scenario possible?
I tried creating a Measure using ISFILTERED to get the level of hierarchy but couldn't figure out a way to use that as a filter inside table.
Thanks a lot!
vidhyasankar
My suggest is just include the filter column in the Hierarchy. Otherwise you may need to create a distinct table with a few measures making measures to filter to the selected slicer.Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
3 Replies
samdthompsonMemorable Member
Hello, heres a really good explanation of creating the heirarchy you need:
https://www.daxpatterns.com/parent-child-hierarchies/
Thanks a lot for the response! The link shows calculation of measures based on parent / child hierarchy. In my requirement there are no numerical values. Just apply extra filter to exclude cetrain rows if a child member is selected.
vidhyasankar
My suggest is just include the filter column in the Hierarchy. Otherwise you may need to create a distinct table with a few measures making measures to filter to the selected slicer.Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
