Hi
I’m trying to set up Row Level Security on a report based on HR Data
And I am trying to do an org chart for the organisation.
The Way it works:
An employee has a position number, and that position number reports to another position number.
The issue is:
An employee can be acting in another position (Thus have two position numbers)
An Employee can be in two or more positions and thus have multiple positions.
A position can have multiple employees
A position with multiple employees can be job shared and thus position reports to only one position has two people split (e.g. one on Mon-Wed and the other Thu – Fri)
Users on leave (either Long Service, Annual, Merterniaty are technically in their position, but someone else would be acting)
With the above, another person might be acting in their position, or they are undertaking both positions/roles.
Has anyone come across this and worked out how this can be achieved as I have used the PATH and LOOKUPVALUE, but it gets stuck when there are multiple values in return, and it expects only one to be returned.
Eg
Path =
PATH (
'EmployeeInfo'[PositionNumber],
'EmployeeInfo'[ReportsTo]
)
Level 1 =
LOOKUPVALUE (
'EmployeeInfo'[Employee_Name],
'EmployeeInfo'[PositionNumber], PATHITEM ( 'EmployeeInfo'[Path], 1, 0 )
)
When I get to level 3 is when I get multiple and I have a seven levels
