Forum Discussion

_chris_'s avatar
_chris_
Helper III
2 years ago
Solved

RLS question

Hi,   I need an RLS expression which I want to assign to a table and which has these rules:   Condition 1: [Field A] = "blabla" OR Condition 2: [CostCenter] = ...     Condition 2 is hard for...
Need Help
  • AnalyticsWizard's avatar
    AnalyticsWizard
    2 years ago

    _chris_ 

     

    To implement the Row-Level Security (RLS) with the conditions you’ve mentioned, you can use the following DAX expression:

    [Field A] = "blabla" || 
LOOKUPVALUE(
    dimAccessRights[CostCenter], 
    dimAccessRights[StaffId], 
    LOOKUPVALUE(
        dimEmployee[StaffId],  
        dimEmployee[EMail], 
        CONCATENATE(LEFT(USERPRINCIPALNAME(), FIND("@", USERPRINCIPALNAME())), "mydomain.de")
    )
) = [CostCenter]

    This expression checks if Field A is “blabla” or if the CostCenter associated with the StaffId (which is looked up using the USERPRINCIPALNAME) matches the CostCenter in the current row of the table.

    Please replace dimAccessRights with the actual name of your access rights table. Also, ensure that the relationships between the tables are correctly set up in your data model.

    Remember, RLS filters are applied at the row level and are always enforced in the context of the user viewing the report. Therefore, the data returned by this expression will be different for each user based on their USERPRINCIPALNAME.

    I hope this helps! Let me know if you have any other questions. 😊

Wilson_'s avatar
Wilson_
Memorable Member
2 years ago

Hi Chris,


Can you share what your data model looks like? That's a fairly important piece of the puzzle when talking about row level security.

_chris_'s avatar
_chris_
Helper III
2 years ago

Hi Wilson, Thanks for getting back to me.

 

At the moment, it works like this:

 

The RLS formula is for the FilterUser table: 

[PersonalId]=LOOKUPVALUE(dimMitarbeiter[PersonalId],  dimMitarbeiter[EMail], CONCATENATE(LEFT(USERPRINCIPALNAME(), FIND("@", USERPRINCIPALNAME())), "xxx.de"))
 
This table filters the dimKstRechte table. Here I have multiple line per employee saying for an employee which cost centers he is allowed to see.
 
This table filters the cost center table which finally filters the actual fact table holding the real data.
 
This works since years.
 
Now I have a new requirement: Certain records of the fact table should be seen by everyone. This new rule can be easily describend with [KstAuswertung1]="bla".
 
This could be achieved by appending many rows per employee to the dimKstRechte table, because also the row for the new criteria do have a certain cost center. But that would be too much work and is not easy to maintain.
 
So my idea is not to use this filter chain anymore but to directly put an RLS formula to the fact table.
 
I hope you can understand my way of thinking and help me.
 
Thx, Christian