Using RLS employee should see data for his organizational group as well as all his reportees data

[Anonymous]

Below is the Employee table showing Emp id , emp name email,Organization group, manager id , name and email. Each employee is assigned to more than one group (here 2 groups).

Another is the sales table showing sales data for employee.

When an employee "Leia Organa" view the report she should see data for the groups she is assigned (Technical chief group ,cloud computing group) as well as her reportees data.

reportees Hierarchy.

This should be the final result in powerbi. 

Immediate help would be appreciated!

[Anonymous]

Thanks for you reply @Chakravarthy . But here it shows data for only that particular employee. It doesnt show data for their reportees. If I select " Leia Organa", it should show data for 1.  " Leia Organa" 2. Kylo Ren 3.Darth Maul.   Leia Organa is Manager of Kylo Ren and Darth Maul.

[Chakravarthy]

@Anonymous When I select "Leia Organa" It is showing "Kylo Ren" and "Darth Maul" Below is the screenshot 

[Anonymous]

@Chakravarthy .Kindly ignore previous reply\post since it worked !. Ihere was issue in relationship between Distinct_emp and Bridge table. After I correct that it worked !!Thanks. Accepted as solution. Thanks for your immediate help!.

[Anonymous]

Hi @Chakravarthy , I have created tables and relatioship as shown by you. But it is not working.

Not sufre where it goes wrong.

Could you please send your .pbix file to refer?

[Chakravarthy · Accepted solution]

Hi @Anonymous 

This should be model

This should be Joins between tables:

Table DAX Code:

1. DISTINCT_EMP = DISTINCT(Employee[Employee Email])

2. BRIDGE =

VAR TABLE1 =

            SUMMARIZECOLUMNS(

                Employee[Employee Email],

                Employee[Manager Email]

            )

VAR TABLE2 =

            SUMMARIZE(

                Employee,

                Employee[Employee Email],

                "Manager Email", Employee[Employee Email]

            )

RETURN

UNION

(

    TABLE1,

    TABLE2

)

3. BRIDGE1 = DISTINCT_EMP

Please check this "Apply Security  Filter on Both Sides" when joining BRIDE and BRIDGE1

In Manage Roles:

Hope I answered your RLS. If so, please provide Kudos and encourage me.