Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to use Hierarchy table in a filter visual

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]))

 

Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    Hi, Anonymous 

    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.

4 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    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.

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Anonymous 


    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        v-angzheng-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi, Anonymous 

        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.