Forum Discussion

z-rising's avatar
z-rising
Frequent Visitor
2 years ago

RLS Security with 2 categories / restrictions

Currently I have a working report with RLS.  I am using emails for the security for users accessing through Power BI Service.  My RLS Table has names, divisions they can have access to, and emails fo...
Need Help
aduguid's avatar
aduguid
Icon for Memorable Member rankMemorable Member
2 years ago

You could build it out as an excluded account. 

 

RLS Table Example:

Email Division ExcludedAccount

[email protected]Division1AccountA
[email protected]Division2 
[email protected]Division3AccountA
[email protected]Division4 
[email protected]Division1

 

RLS DAX Filter

 

[Division] IN VALUES(RLS[Division]) &&
(
    ISBLANK(RLS[ExcludedAccount]) ||
    NOT ([Account] = RLS[ExcludedAccount] && [Division] IN {"Division1", "Division2", "Division3", "Division4"})
)

 

 

z-rising's avatar
z-rising
Frequent Visitor
2 years ago

I am getting the error: The synatax for '[Division]' is incorrect

 

Here is what I have in the Manage Security Roles DAX editor for the role I created on the RLS table

 

[Email] == USERPRINCIPALNAME()
[Division] IN VALUES (RLS[Division]) &&
(
    ISBLANK(RLS[ExcludedAccount]) ||
    NOT ([Account Name] = RLS[ExcludedAccount] && [Division] in {"North America", "South America", "Europe", "Asia"}
    )
 
I updated my RLS table to have 4 columns - "Username" "Division" "Email" "Excluded Account"
 
The RLS table has "Store Losses" in the ExcludedAccount column for every row that has either North America, South America, Europe, or Asia in the Division column.
 
The first line of code is obviously the original row level security to limit access based on email matching division (some users are repeated with more than one division)
 
The two data tables that everything comes from use the columns "Account Name" for the first and "Account Descrpition" for the second that I am trying to reference against, so I may need to do the code twice once it's working.
 
The only other thing I can think of is that the table the RLS flows through into the two data tables is named "Division II" becasue of replication from other table relationships, I just made a new one specifically for the RLS purpose.