Hierarchy Chart (Org Chart) & Row Level Secutity (RLS)

Anonymous — Wed, 21 Oct 2020 01:22:59 GMT

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.

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

Re: Hierarchy Chart (Org Chart) & Row Level Secutity (RLS)

Anonymous — Wed, 21 Oct 2020 01:57:21 GMT

Hi amitchandak

Thanks for the response, and I have used the radacad code.

But that works on a one to one

A position number reports to one position number, but

I want to display the employee name as well which could be 2 or more employees.

topic Re: Hierarchy Chart (Org Chart) & Row Level Secutity (RLS) in DAX Commands and Tips

Hierarchy Chart (Org Chart) & Row Level Secutity (RLS)

Re: Hierarchy Chart (Org Chart) & Row Level Secutity (RLS)

Re: Hierarchy Chart (Org Chart) & Row Level Secutity (RLS)