Power BI DAX Filter Matrix/Table Based on Slicer Hierarchy Level

[vidhyasankar]

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!

[V-pazhen-msft · Accepted solution]

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

[samdthompson]

Hello, heres a really good explanation of creating the heirarchy you need:

https://www.daxpatterns.com/parent-child-hierarchies/

[vidhyasankar]

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.