Dynamic row level security not working on service

[Anonymous]

Hi All I really need help before I need to put a static process in place. I have spent days researching trying to get it to work. 

Link to test file: Test data file

I have a test data table that looks like this with sensitive survey data (cut down version):

Responses	Surveys	Short_Question	Year	Term_All	Faculty_	School_Clean	CourseCode_Description
20	10	Q1	2020	2020 Summer	Faculty 4	School 4	Course 1
20	10	Q2	2020	2020 Summer	Faculty 4	School 4	Course 1
20	10	Q3	2020	2020 Summer	Faculty 4	School 4	Course 1
20	10	Q4	2020	2020 Summer	Faculty 4	School 4	Course 1
20	10	Q5	2020	2020 Summer	Faculty 4	School 4	Course 1
10	12	Q4	2020	2020 Summer	Faculty 1	School 1	Course 2
10	12	Q5	2020	2020 Summer	Faculty 1	School 1	Course 2
300	60	Q1	2020	2020 Summer	Faculty 3	School 2	Course 3
300	60	Q2	2020	2020 Summer	Faculty 3	School 2	Course 3
300	60	Q4	2020	2020 Summer	Faculty 3	School 2	Course 3
300	60	Q5	2020	2020 Summer	Faculty 3	School 2	Course 3
590	29	Q1	2020	2020 Summer	Faculty 2	School 5	Course 4
590	29	Q2	2020	2020 Summer	Faculty 2	School 5	Course 4
590	29	Q3	2020	2020 Summer	Faculty 2	School 5	Course 4
590	29	Q5	2020	2020 Summer	Faculty 2	School 5	Course 4
700	13	Q1	2020	2020 Summer	Faculty 2	School 3	Course 5
700	13	Q2	2020	2020 Summer	Faculty 2	School 3	Course 5
700	13	Q3	2020	2020 Summer	Faculty 2	School 3	Course 5
700	13	Q5	2020	2020 Summer	Faculty 2	School 3	Course 5

I have a users table like this:

School_Clean	Username
School 1	user1@test.com
School 2	user2@test.com
School 3	user3@test.com
School 4	user3@test.com
School 5	user5@test.com

I will put the real emails in before I test in the service. 

I am trying to ensure that user1 sees all data for School 1 etc, but nothing I do works when it gets into services. 

have added the emails to the role correctly in security within the service. 

I have implemented roles and it all works fine in desktop when I view as role, however it is not working in the desktop no matter what I do. Just the header of the table is shown with no data. I

I have tried multiple approaches, the latest one includes the following code:

CONTAINS('SECURITY (2)', 'SECURITY (2)'[Username], USERPRINCIPALNAME(), 'SECURITY (2)'[School_Clean], 'TEST'[School_Clean])

I really need some help!

[Anonymous]

@Anonymous 
Once you have a list of email address of users, you should try a simple code in RLS:

Username = Userprinciplename()

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks yes I have tried that on the data table, I can 'View as' on the desktop and everything works as expected. And when I publish to the service, add the email addresses to the roles, and then test as role, I cant see anything. Its just a bit weird! 

[Anonymous]

@Anonymous 

Your dynamic RLS should have worked properly, although you cannot see value with test as role.

With my test, I also get blanks with "test as roles". To make it work properly you need to enter the selected person. Otherwise power bi service cannot determine whose view do you want among many users so returns blank.

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Paul, 

Yes I did enter the name of the person in. Still cant see anything.

[jdbuchanan71]

@Anonymous 

Have you added the users to the roles in the service?

Once you have published, on PowerBI.com you go to datasets > security and add users to the role.

Keep in mind that if a user is a member of the workspace the RLS will not apply to them.

An article that might help some: https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls

[Anonymous]

Hi Yes, I have added the role in the service and tested as that role, when I test as role this is where the table headers appear but no data. 

[Anonymous]

Hi, if you happen to still be working on this two years later, or for anyone else that gets this as the first result:

The Usernames might not be what you think it is. In a medium article I found this hint:
"

Identify user in hierarchy

User Principal Name function, USERPRINCIPALNAME(), is the main method for identification. Note that this function return different values in desktop and in online service. The online name is what matter during RLS execution."

I had the same problem, and I was using first.last@companyname.com, but the actual IDS in Power BI were companyid@companyname.com . So if you test first.last@companyname.com in desktop it will work, but when you test the individual in live, it will use their ID email, and it won't work.

Hope this helps someone!