Row Level Security exclusions

[nrichards_au]

I don't think it's possible but I'm putting it out there.

I've got a fact table that has RLS against the DEPT ID field. I was wondering if it's at all possible to exclude RLS against the particular column?

The values in column Org need no restrictions as it's accessible organisation wide

BU ID	STAFF ID	Org	Div	BU
1	A	T	T	T
2	B	F	F	F
3	C	T	F	F

[nrichards_au · Accepted solution]

Thanks everyone for the responses.

It wasn't that I didn't know how to create a solution more wondering if RLS could be ignored against a particular column in spite of it being against the nature of RLS.

I'm dealing with fact tables of about 500M rows so wanted to limit duplicate aggregation tables if possible but don't think it is possible

[Anonymous]

Hi @nrichards_au ,

May I ask if you have resolved this issue?  If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Anonymous]

Hi @nrichards_au ,

May I ask if you have resolved this issue?  If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Also thankyou @MarkLaf  for your inputs.

Thank you.

[MarkLaf]

You need to duplicate the column in a calculated table that doesn't get filtered by RLS. If not entirely applicable to your scenario, this SQLBI article walks through the general pattern: https://www.sqlbi.com/articles/computing-accurate-percentages-with-row-level-security-in-power-bi/

Is there a particular calculation you need to achieve? To provide more help, I think we'd need a little bit more context related to your model incl dummy data + desired output you want.

[Anonymous]

Hi @nrichards_au ,

I wanted to check if you had the opportunity to review the information provided by @Akash_Varuna . Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[Akash_Varuna]

Hi @nrichards_au You could achive this thorugh DAX or BY moving the rquired to a dim table please try these 

1. Custom RLS Filter in DAX

   • Apply a filter that allows unrestricted access to rows based on Org:

     [DEPT ID] = "AllowedDeptID" || [Org] = "T"

2. Separate Roles for Exclusions

   • Create two roles: one with RLS for restricted users and another unrestricted for users who need access to Org.
     You could also remove the Org column from Fact and create a Dim table and apply rls to fact only not to Org
     If this post helped please do give a kudos and accept this as a solution
     Thanks In Advance