RLS to show all records of department to which an employee belongs to

[VeenaAnalyst]

I want to show all records of department to which an employee belongs to as well as direct and indirect reportees.

I was able yo achieve the direct ad indirect reportees part but unable to show all records of department.Below is the table structure.

1.If Leia Organa logs in the system she will see data for direct reportees Kylo Ren and Darth Maul and indirect reportees Ana. This is achieved.

2.Now 2nd requirement is Leia Oragana should see data for all records having similar department i.e Dept 3

So It will include Obi-wan .

3.3rd requirement there is another table department where the employees are not enterd in Manager table , but since they belong to some department ,employees from Manager table should see related department data from department table. Robert will be included.

Final output should be 

Immediate help would be appreciated!

[VeenaAnalyst]

I joined 2 filters using or condition and it worked for first 2 conditions.

[Department] = Calculate(MAX([Department]),FILTER(Manager,[Employee email] = USERPRINCIPALNAME())) || PATHCONTAINS([Hierarchy path], [current userid])

Now we are left with one requirment. record from department table. Robert form department table should also reflect. For that could anyone suggest the join/model.

[VeenaAnalyst]

Additonl info for what i achieved with screenshot : I am getting expected output for first requirment direct and indirect reportees using Sales table filter
PATHCONTAINS([Heirarchy path id], [current userid]) = True

Getting expected output for second requirment. using filter [Department] = Calculate(MAX([Department]),FILTER(Manager,[Employee email] = USERPRINCIPALNAME()))

But If I used both its elimating records and showing data for kylo ren and leia.

ALso 3rd requirment is it should show records from department table also. Let me know the joins toachieve this

[Greg_Deckler]

@VeenaAnalyst Can you post your sample data as text?

[VeenaAnalyst]

Thank Greg!

Below is sample data.

Manager table:

Sales Table:

Department Table:

I have created two measure user id and Current user id
1.user id = USERPRINCIPALNAME()

2.current userid = LOOKUPVALUE(Sales[Employee ID],Manager[Employee email],Manager[user id])

Also I created calculated column
Heirarchy path id = PATH(Sales[Employee ID], Sales[Manager ID])

Create one Role with 2 filters
Sales table filter
PATHCONTAINS([Heirarchy path id], [current userid]) = True

Manager table filter
[Department] = Calculate(MAX([Department]),FILTER(Manager,[Employee email] = USERPRINCIPALNAME()))

If I create only sales table filter then I can see data for reportees and direct reportees for Leia Organa
If I create only Manager table filter then I can see all records for same department for Leia Organa
But If I applied both its not giving correct data.

Also there is one department table with dept name and emp id. My report should show record from the department table as well.

So If Leia Organa logs in below should be final output.