Dynamic RLS for an organizational structure (hierarchy)

[zargawy]

Hi guys,

I am pretty new to PowerBi 🙂

I am working on a demo project

I have an "employee" table with the following columns:

employee_name, employee_id, employee_mail, manager_name, manager_id, manager.mail

I have another table "tasks" with the following columns:

task_id, employee_assigned_id, start_date, end_date

using PATH function I created the organizational structure of the company and added other calculated columns to the "employee" table such as: level_01_employee_name, level_02_employee_name, ..... , level_07_employee_name

after that I used the following DAX expression to filter the "employee" table from the manage roles window:

PATHCONTAINS(
    SELECTCOLUMNS(

    FILTER(
        employee,
        employee[employee_mail] = USERPRINCIPALNAME()
    ),
        "path_ids", employee[path_ids]
    ),

    MAXX(
        FILTER(
            employee, 
            employee[employee_mail] = USERPRINCIPALNAME()
        ), 
        employee[employee_id]
    )
)

using Microsoft Gantt Chart I create a Gantt Chart that shows the tasks for each employee during a certain period of time. this is a sample:

now I tested the roles for one of the enigeers and it works! I only see the task assigned to the engineer

and in the organizational chart (created using Hierarchy Slicer visualization) I see the whole map leading from his manager down until the engineer (but the engineer is only able to see his name and his manager and the manager of his manager and so on, but not the details of other engineers from his team or other teams) so this works.

now I tried testing using one of the managers email adresses and it does not work at all. I see nothing. I expected to see in the gantt chart all tasks assigned to his team members. Also, in the hierarchy chart I see the manager name and his managers also upstream. but when I expand the manager entry I see also nothing, where I expected to see names of all his team members. 

this is the relationship between the employee (the one side) and the tasks (the many side) tables

what is the problem here? can someone help me? 

thanks

[zargawy]

I looked into most of the online available tutorials (such as https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi)I followed his and others methodologies but it did not help. 

** one last note: the employee table is being actually loaded from the Active Directory.