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

AnalyticsWizard's avatar
AnalyticsWizard
Solution Supplier
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. 😊

  • _chris_'s avatar
    _chris_
    Helper III
    2 years ago

    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 😉