RLS question

[_chris_]

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 me to even explain:

1. Lookup the PersonnelID of the USERPRINCIPAL. For this, I have the code:

LOOKUPVALUE(dimEmployee[StaffId],  dimEmployee[EMail], CONCATENATE(LEFT(USERPRINCIPALNAME(), FIND("@", USERPRINCIPALNAME())), "mydomain.de"))

2. Now with the StaffId lookup another table which has the access rights for StaffId towards CostCensters

StaffId, CostCenter

One StaffId can have the right to access the data of multiple cost centers.

Thx for you help

Christian

[AnalyticsWizard · Accepted solution]

@_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. 😊

[_chris_]

Hi,

I am super happy, because you really understood what I need and it works during the first attempt (Ahem, I have to admit that I got an error first and needed to change one relation).

Thanks a lot and have a great sunday!

Christian

P.S.: I will try to analyze what you did so that in addition to a solution I also will learn something 😉

[Wilson_]

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_]

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