cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
k3rz0rg
Advocate III
Advocate III

RLS logic help!

Hi all,

 

I don't do RLS much so I need some help regarding the RLS logic.

I have an extracted table from usage metrics which has user principal names or emails they used to login in a column and I want from those emails, two people will be able to see all and rest of them will not.

I created a custom table with only those email addresses and if value from the userprincipal name matches with the login email (username()) then the report will show everything else nothing (or just the logged in persons info only).

 

Usage Metrics Table sample.png

 

The red marked columns are custom columns. Does anyone know how can I set the logic for the roles?

I added those role and role flag column to try if the logged in person is authorized (following corresponding user principal name) or role flag is 1 then the user will see all otherwise nothing.

I used role flag for this following a youtube video but didn't work and the column returns multiple 1s so maxx proved not to be a good idea.

 

//if the username() is 1 then the person is authorized to see all data else the person will only see his/her data in this case.

If(
   MAXX(
              Filter('Contacts_PermSource','Contacts_PermSource'[User Principal Name] = Username()),'Contacts_PermSource'[Role Flag]) = 0,
'Contacts_PermSource'[User Principal Name] = Username(),
1=1)

So I dropped this idea.

 

And also since that workspace has 30+ users, how can skip going through the trouble of adding the unauthorized users one by one?

 

Any help would be much appreciated.

 

Thanks,

k3.

 

1 ACCEPTED SOLUTION
GilbertQ
Super User
Super User

Hi there

It sounds like you need Dynamic RLS, which makes it easier to manage

I suggest you read and try out this blog post from Kasper. There is a good working example which you can try and once you get that working, you can then apply it to your data.

https://www.kasperonbi.com/power-bi-desktop-dynamic-security-cheat-sheet/




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

5 REPLIES 5
GilbertQ
Super User
Super User

Hi there

It sounds like you need Dynamic RLS, which makes it easier to manage

I suggest you read and try out this blog post from Kasper. There is a good working example which you can try and once you get that working, you can then apply it to your data.

https://www.kasperonbi.com/power-bi-desktop-dynamic-security-cheat-sheet/




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

Hi @GilbertQ ,

 

thanks for your response and tutorial and pardon my delayed response, i was out for 4th july vacation. I followed the similar procedure as the link suggested but when I test that rule with username offline (on desktop version while working on it), it works fine, once I publish it to the workspace and test it from there ( dataset > security > view/test as > username/email ) it does not filter and show all the data except one or two email addresses. I tried both username() and userprincipalname() and same outcome everytime. 

Initially I thought maybe because some of them workspace admins so I changed their role from admin to just member as well but no luck. I even tried making one of those email id holders login from his computer and he was able to see all the data where he was not supposed to.

Do you have any idea why is this happening? Is there any PBI server glitch or something?

Hi there

If the users are members of the App Workspace V1, they are treated as Admins and will always see all the data.

You will need to remove the users from the App Workspace and share it via an App.

Or you can use the App Workspace V2 and assign the users a Viewer role.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Hi @GilbertQ ,

 

pardon my delayed response and yes I noticed that admin/member role issue with the older version of workspace  too and I believe, MS still need to work on the v2 as well. To avoid complication for the time being with such small report I created, I am just following the link you provided.

While I was working with dynamic RLS, I found the functions username() and userprincipalname() are the same (or at least giving me the same output in this case), do you know any differences between these two functions?

 

Thanks, 

K3.

Hi there

Yes I do know the difference.

If you use USERNAME() and you are logged into a Domain controlled PC it will return DomainName\UserName

If you use USERPRINCIPALNAME() and you are logged into a Domain controlled PC it will return user@domain.com

When you are on the Power BI Service, it uses the USERPRINCIPALNAME() I would suggest using the USERPRINCIPALNAME()




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors