Dynamic RLS - Children with missing Parent Keys

[gil12]

Hi All,

I'm trying to Implement a Dynamic RLS at my organization. I have gotten the employee structure from ActiveDirectory. It works perfectly fine for majority of the cases, except of the ones who are missing Parents/Managers. Hierarchy in path column using Path function would just stop there, it wouldn't even propage to Highest level that is to the CEO.

For example - Let's say directory has managers from two deparment reporting to him and through managers he would also see employee records who report to those managers. Now the problem arises if one of the department's manager's postion is vacant. Then neither diretor is not seeing those deparment's employee records nor VP's, CEO are able to see those employee records.

Can anyone please help me mitigate this issue, if anyone has faced this issue? Thanks in advance!

In Below data LAD02 is CEO, but due to missing ParentID for some Employees, HierarchiesPath is not going up to that level. Basically what i need is, if an deparment FO supervisor is missing, that employee should automatically report to Director and if directos is missing, employee should automatically report to VP and so on....

I'm using below Dax code for RLS (ignore name of table and columns as it's from other table)

PATHCONTAINS(account[Path],
MAXX(
Filter(account,
account[organizationalPerson.mail] = USERPRINCIPALNAME()),
account[PeopleSoftChildID_int]))

[Greg_Deckler]

Sounds like you should implement a calculated column that performs the necessary Parent ID lookup?

[gil12]

Yes, that's what i thought but can't think of how to implement it! Any help or guidance will be appreciated

[Greg_Deckler]

If you can post some sample data as text and point me towards the issues I can take a look.

[gil12]

https://drive.google.com/file/d/19UYOzSwrV1M_vl9H1JFRMnyyvSyuhzl5/view?usp=sharing 

Here's a file link in google drive!

Thanks for your time and help in advance!