cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bideveloper555
Helper IV
Helper IV

Avoid RLS to show some measures

hi

i have Date table, Fact table ,Dim table and User table.

i did create some YOY measure on Fact table .

i have 2 visuals. 1 is card showing YOY and 1 is table with sale order information

 

Card with out RLS(entire fact table YOY )

Table data with RLS applied.(only user Specific data)

 

Thanks

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
bideveloper555
Helper IV
Helper IV

hi im really sorry to bother.

i been struggling to implement 2 functionality.

Both are RLS related.

1 . As i been above.

2. i dont want to add every single email addres in power bi service to give permission.

bideveloper555_0-1633445054254.png

 

user table:

ID  emailaddress           admin 

1   abc@test.com           0

2    def@test.com           1

 

I need to implement RLS,we have 1000s of user and every user need to see the data which user name is connected to fact table but some user(lets say admin) need to see all the users data.

if i need to make group per user, i can make it but just need to know. how every thing is connected.

i been using USERPRINCIPALNAME () in manage roles.

 

Thanks in advance.

 

 

View solution in original post

7 REPLIES 7
bideveloper555
Helper IV
Helper IV

hi im really sorry to bother.

i been struggling to implement 2 functionality.

Both are RLS related.

1 . As i been above.

2. i dont want to add every single email addres in power bi service to give permission.

bideveloper555_0-1633445054254.png

 

user table:

ID  emailaddress           admin 

1   abc@test.com           0

2    def@test.com           1

 

I need to implement RLS,we have 1000s of user and every user need to see the data which user name is connected to fact table but some user(lets say admin) need to see all the users data.

if i need to make group per user, i can make it but just need to know. how every thing is connected.

i been using USERPRINCIPALNAME () in manage roles.

 

Thanks in advance.

 

 

bideveloper555
Helper IV
Helper IV

hi

 

i did create second fact table and created measure but fact table 2 i didnt make any relationship to user table. thinking this would work.

but it didnt work.

Are there any resource on this scenario?

Hey @bideveloper555 ,

Sent along the message in our private conversation, but for those who might find this thread later on here's what I shared as to how to get this working in this particular situation: 

I walked through this and all you really need to do is have 2 fact tables.

1 with RLS on it and 1 without RLS on it. 

Usually, the USER is something like a UPN that you might enforce the RLS on so whenever someone logs in they would just see their own information in the table visual and then the name card comes from the FACT table without RLS. 

Here's a dummy report I built to show how it works, you'd need to go into the modeling tab and view as to see the RLS in action.

Let me know if you have any questions.

https://drive.google.com/file/d/1pDzwjo8EBr9FB6QL9wCFjTLREFkvvw5N/view?usp=sharing

This worked and ignore my previous message.

Thanks sirlanceohlott

hi, model which you built was working perfectly but i cant set up RLS on Fact table but i need to set up on user table.

https://drive.google.com/file/d/1JEz0ZaL5qzXI8Ht9coGLrdRFHGCbeGSH/view

i am attaching the sample model.

bideveloper555_0-1633691547113.png

 

Hey @bideveloper555 ,

Probably need to make a relationship between the user and FACT table 2 you've created and then create the new RLS management level group so all your users would be able to see the information for the page you've created!

 

Not sure if I've seen any resources on this, but if I've got some time later on I might be able to make a dummy report that would show the process.

sirlanceohlott
Advocate III
Advocate III

Hey @bideveloper555 ,

 

What you could do as a workaround is you could have 2 FACT Tables, 1 with RLS applied to specific users & 1 with RLS for the card and everyone is in a management level group where they can see all values.

 

This would be able to get around the issue you are experiencing where you want 1 visual to be RLS and the other visual card to be the entire FACT table.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.