Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
_chris_
Helper III
Helper III

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

 

 

1 ACCEPTED SOLUTION
AnalyticsWizard
Super User
Super User

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

View solution in original post

4 REPLIES 4
AnalyticsWizard
Super User
Super User

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

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_
Super User
Super User

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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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 
 

_chris__0-1712472228456.png

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.