USERPRINCIPLENAME for direct and indirect employees

[martinvarbanov]

I have a table Users:

manager_email	employee_name	Path
mng3@abc.com	Employee 1	mng1@abc.com|mng2@abc.com|mng3@abc.com
mng3@abc.com	Employee 2	mng1@abc.com|mng2@abc.com|mng3@abc.com
mng3@abc.com	Employee 3	mng1@abc.com|mng2@abc.com|mng3@abc.com
mng3_1@abc.com	Employee 4	mng1@abc.com|mng2@abc.com|mng3@abc.com|mng3_1@abc.com
mng3_1@abc.com	Employee 5	mng1@abc.com|mng2@abc.com|mng3@abc.com|mng3_1@abc.com

There is RLS on the table Users: CONTAINSSTRING ( [Path], USERPRINCIPALNAME()).

I want to have 2 pages on the report:

1) All employees - this is working fine based on the RLS.

2) Direct employees - I want to apply page filter where USERPRINCIPALNAME() = Users[manager_email]. How I could achive that?

[v-zhangti]

Hi, @martinvarbanov 

To create a page filter in Power BI that shows only the direct employees of the current user, you can follow these steps:

1. Create a new page in your report and add a table visual to it.
2. In the Fields pane, drag the 'employee_name' and 'manager_email' columns from the 'Users' table to the 'Values' section of the table visual.
3. In the 'Visualizations' pane, click on the 'Filters' icon and select 'Add filter'.
4. In the 'Filters' pane, select the 'manager_email' column and choose 'Basic filtering'.
5. In the 'Basic filtering' pane, select 'is' and then select 'Users[manager_email]' from the dropdown list.
6. Click on the 'Apply filter' button to apply the filter to the visual.

This will create a page filter that shows only the direct employees of the current user based on the 'manager_email' column in the 'Users' table and the RLS applied to it.

Note that this assumes that you have already set up the RLS on the 'Users' table using the 'CONTAINSSTRING' function as you mentioned. If you have not done so, you will need to set up the RLS first before the page filter will work correctly.

For reference:

Row-level security (RLS) with Power BI - Power BI | Microsoft Learn

Solved: Dynamic RLS - Microsoft Power BI Community

Row Level Security with SSAS Tabular Live Connection in Power BI - RADACAD

Best Regards,

Community Support Team _Charlotte

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