How to use Hierarchy table in a filter visual

[sellyeha]

I have a table of employees and their managers with the path function as a new column(top left in picture)

I have a table of data (bottom of picture). 

What I want is to have the filter visual to display all employees under the selected name in the hiarachy. Example, when Walter White is selected in the filter, the expected result in the bottom table should be Jesse Pinkman, Saul Goodman, Walter White. 

I've implimented hierachy RLS in the modelling section using user principal name in a seperate document. Could one use PATHCONTAINS and MaxX and adapt it to a filter visual somehow...?? I can't wrap my head around it. I have the code that works in the modelling section... 

 
PATHCONTAINS(Employees[Path],MaxX(Filter(Employees,[Email]=USERPRINCIPALNAME()),Employees[ID]))

[v-angzheng-msft]

Hi, @sellyeha 

1. Create a calculation table to summarize the names

Name = SUMMARIZE('Table',[Name])

2. Create a measure as follows, and filter the items with measure is 1 in the filter pane.

Measure = 
var _selectedName=SELECTEDVALUE('Name'[Name])
var _ID=CALCULATE(MAX('Table'[ID]),'Table'[Name]=_selectedName)

var _t1=
    SUMMARIZE(FILTER(ALL('Table'),'Table'[ID]=_ID),[ID])
var _t2=
    SUMMARIZE(FILTER(ALL('Table'),'Table'[Manageer ID]=_ID),[ID])
var _T=UNION(_t1,_t2)
var _IsIn=IF(MAXX('Table',[ID]) in _T, 1,0)

return _IsIn

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sellyeha]

This is great. It seems that this will go down only 1 level. Can this be adapted for n levels in a tree? Say for instance that if we added "Skinny Pete", who's boss is Jesse Pinkman. 

[v-angzheng-msft · Accepted solution]

Hi, @sellyeha 

Try this:

Measure 2 = IF(MAX('Table'[Name]) in 
SUMMARIZE(
FILTER(ALL('Table'),CONTAINSSTRING([_Path],SELECTEDVALUE('Name'[Name]))),[Name]),1,0)

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Please show the expected outcome.