RLS Help - What method is best

[Anonymous]

Hi,

Creating a new thread as need more basic advice on what way is best to implement RLS for my scenario. The RLS Table would look like this:

Email	User	Manager	Account	Region	Country	Transaction Type
User1@usermail.com	User 1	User 1		EMEA
User2@usermail.com	User 2	User 1	Word	EMEA	France
User2@usermail.com	User 2	User 1	Excel	EMEA	Germany
User3@usermail.com	User 3	User 3			United Kingdom
User3@usermail.com	User 3	User 3			Ireland
User4@usermail.com	User 4	User 3	Outlook		United Kingdom
User4@usermail.com	User 4	User 3	Outlook		Ireland
User5@usermail.com	User 5	User 5		EMEA		Resell
User6@usermail.com	User 6	User 5			Germany	Resell

My Data has corresponding fields for Manager | Account | Region | Country | Transaction Type.

What I need is when a User logs into PowerBi they can only see the corresponding portions from above. If one of the fields is blank in the Rule table ie Account then I want no filter to apply at all to Account for that user. When there are multiples completed then I want each rule to be applied to the user.

So User2 should only see User 1's subset and Accounts Excel & Word Countries of France and Germany (Region EMEA).

So questions:

Should I split the above RLS table into 5 separate tables with the Email as the common index (repeated for users if say they cover 2 countries)

What is the best way of implementing rules like this?

I tried creating a rule for say Account then Country and put a user in both rules but in Desktop simulation of that user if both rules are on then NO rules applied at all.

Thanks for any and all advice.

[v-eachen-msft]

Hi @Anonymous ,

I prefer to use single table.

You could refer to the following DAX:

'Table'[Region] in 
CALCULATETABLE(SELECTCOLUMNS('List',"A",List[Region]),List[Email]=USERPRINCIPALNAME())
&&
'Table'[Account] in 
CALCULATETABLE(SELECTCOLUMNS('List',"A",List[Account]),List[Email]=USERPRINCIPALNAME())

[Anonymous]

Thanks a million! and how do I account for scenarios where the field in the RLS table is blank? In those scenarios I want no filters applied to that field for the user?

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

You could refer to the following DAX:

if(
USERPRINCIPALNAME()  in 
SELECTCOLUMNS('List',"A",List[Email])
,
'Table'[Region] in 
CALCULATETABLE(SELECTCOLUMNS('List',"A",List[Region]),List[Email]=USERPRINCIPALNAME())
&&
'Table'[Account] in 
CALCULATETABLE(SELECTCOLUMNS('List',"A",List[Account]),List[Email]=USERPRINCIPALNAME()),
TRUE()
)