Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors