Avoid RLS to show some measures

[bideveloper555]

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

[bideveloper555 · Accepted solution]

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.

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]

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?

[sirlanceohlott]

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

[bideveloper555]

This worked and ignore my previous message.

Thanks sirlanceohlott

[bideveloper555]

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.

[sirlanceohlott]

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]

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.