Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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

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!

Need Help
  • Chakravarthy's avatar
    Chakravarthy
    2 years ago

    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.

5 Replies

  • Chakravarthy's avatar
    Chakravarthy
    Resolver II
    2 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Chakravarthy
      Resolver II
      2 years ago

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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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?