Forum Discussion
Seeing Data under a Hierarchy (NOT Row Level Security)
So I recently built a product for my company using Row Level Security using PATH and an Employee Hierarchy. It works great but they want to see EVERYONE using a Manager filter to see the data by say a high level VP and then everyone else in their VP and switch between hierarchies.
I've been racking my brain on how to do this since it requires 1) Picking a Manager 2) Having the information table filter to everyone whose Manager is in that Hierarchy
I don't want just someone's direct reports but everyone within their hierarchy.
Example
| Employee ID | Employee Name | Manager ID | Manager Name |
| 1 | Employee 1 | 2 | Employee 2 |
| 2 | Employee 2 | 3 | Employee 3 |
| 3 | Employee 3 | 4 | Employee 4 |
| 4 | Employee 4 | 5 | Employee 5 |
| 5 | Employee 5 | 6 | Employee 6 |
| 6 | Employee 6 |
Given this structure, someone logging in can select out of the available managers (2-6) everyone below them in the hierarchy.
So if someone selects Employee 2 under Manager Name, they would see data for Employee 1 and 2
If someone selects Employee 6 under Manager Name, they would see data for Employees 1-6.
This data would then be tied to an attribute table like so:
| Employee ID | Employee Name | Attribute |
| 1 | Employee 1 | A |
| 2 | Employee 2 | B |
| 3 | Employee 3 | C |
| 4 | Employee 4 | D |
| 5 | Employee 5 | E |
| 6 | Employee 6 | F |
So if I select Employee 4 in the Manager Table Filter above, I should see attributes A, B, C, D only. And then be able to click Employee 5 and see attributes, A, B, C, D, and E.
Does anyone have any ideas? Thanks!
3 Replies
- AlexisOlson
Super User
How about this?
ShowAttribute = VAR SelectedID = SELECTEDVALUE ( EmployeeHierarchy[Employee ID] ) VAR FilteredIDs = CALCULATETABLE ( VALUES ( EmployeeHierarchy[Employee ID] ), FILTER ( ALL ( EmployeeHierarchy ), PATHCONTAINS ( EmployeeHierarchy[Path], SelectedID ) ) ) RETURN SELECTEDVALUE ( Attributes[Employee ID] ) IN FilteredIDsIn this screenshot, the slicer is on EmployeeHierarchy[Employee Name] and the two tables do not have a relationship:
I think this is close - however, I need to filter by the Manager Name and on the right table somehow filter to ONLY show those rows where ShowAttribute = True.
- AlexisOlson
Filter the slicer with an IsManager is 1 (assuming manager name is the same as employee name for matching ID values):
IsManager = IF ( SELECTEDVALUE ( EmployeeHierarchy[Employee ID] ) IN ALL ( EmployeeHierarchy[Manager ID] ), 1, 0 )Similarly, you can adjust ShowAttribute to be 1 or 0 and filter for is 1 on the table visual.
