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
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:
Solved! Go to Solution.
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. 😊
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 😉
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.
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:
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
108 | |
102 | |
94 | |
71 |
User | Count |
---|---|
173 | |
134 | |
132 | |
102 | |
95 |