Forum Discussion
RLS Help - What method is best
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:
| User | Manager | Account | Region | Country | Transaction Type | |
| [email protected] | User 1 | User 1 | EMEA | |||
| [email protected] | User 2 | User 1 | Word | EMEA | France | |
| [email protected] | User 2 | User 1 | Excel | EMEA | Germany | |
| [email protected] | User 3 | User 3 | United Kingdom | |||
| [email protected] | User 3 | User 3 | Ireland | |||
| [email protected] | User 4 | User 3 | Outlook | United Kingdom | ||
| [email protected] | User 4 | User 3 | Outlook | Ireland | ||
| [email protected] | User 5 | User 5 | EMEA | Resell | ||
| [email protected] | 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.
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() )
3 Replies
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())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() )