Forum Discussion
Role level Security for Group Alternate Access
| Employee Name | Requester-Approver Email | Lookup Approver Group | Lookup Requester Group | Department | Dept | Invoice Number |
| Aida | [email protected] | 1 | D | A | 13 | |
| Richard | [email protected] | 1 | Q | B | 434343 | |
| Shen | [email protected] | 2 | HR | C | 4343 | |
| Rachel | [email protected] | 2 | Pensions | D | 434343 | |
| Simran | [email protected] | 2 | Pensions | E | 434 | |
| Danks | [email protected] | 3 | Catering | D | 4545 | |
| Erin | [email protected] | 3 | HR | B | 454545 | |
| Jay | [email protected] | HR | t | 434343 |
I have this simple sample table. I want to build a Role (for RLS) that does the followisng
1. Check for columns Lookup Approver Group and Lookup Requester Group. Where the figures match, allow the employees with same code to see rows of matching records. If Aida is signed on, Aida sees her invoice and invoice of Richard because they have same Lookup Approver Group code of 1. Richard is also able to see his invoice and invoice of Aida.
2. Rachel sees her invoice and invoice of Shen and Simran. All 3 see each others invoice
3. Titi sees her invoice and that of Erin.
4. This rule will not apply to Jay i.e. when Lookup Approver Group or Lookup Requester Group is blank.
Thank you for your help.
Hi Anonymous,
if I got it right, this should work.
1. Create a single GroupKey column
In Power Query or DAX (calculated column):
GroupKey =
IF(
NOT ISBLANK([Lookup Approver Group]),
[Lookup Approver Group],
[Lookup Requester Group]
)
Jay will have GroupKey = BLANK(), which is desired.
2. Create an RLS role
Use this DAX filter expression:
[GroupKey] =
CALCULATE(
MAX('Table'[GroupKey]),
'Table'[Employee Name] = USERPRINCIPALNAME()
)
If this works, leave kudos or mark it as solution.
Best regards!
4 Replies
Hi Anonymous,
if I got it right, this should work.
1. Create a single GroupKey column
In Power Query or DAX (calculated column):
GroupKey =
IF(
NOT ISBLANK([Lookup Approver Group]),
[Lookup Approver Group],
[Lookup Requester Group]
)
Jay will have GroupKey = BLANK(), which is desired.
2. Create an RLS role
Use this DAX filter expression:
[GroupKey] =
CALCULATE(
MAX('Table'[GroupKey]),
'Table'[Employee Name] = USERPRINCIPALNAME()
)
If this works, leave kudos or mark it as solution.
Best regards!
Hi Anonymous,
Thank you for reaching out to Microsoft Fabric Community.
Thank you Mauro89 for the prompt response.
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user for the issue worked? or let us know if you need any further assistance.
Thanks and regards,
Anjan Kumar Chippa
Hi @arikofather,
Thank you Rufyda for the prompt response.
We wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.
Thanks and regards,
Anjan Kumar Chippa
Hi,
try thisVAR CurrentUser = USERPRINCIPALNAME()
VAR CurrentUserGroups =
FILTER('Invoices', 'Invoices'[Employee Name] = CurrentUser)RETURN
(
'Invoices'[Lookup Approver Group] IN SELECTCOLUMNS(CurrentUserGroups, "Group", 'Invoices'[Lookup Approver Group])
||
'Invoices'[Lookup Requester Group] IN SELECTCOLUMNS(CurrentUserGroups, "Group", 'Invoices'[Lookup Requester Group])
)
&&
NOT(
ISBLANK('Invoices'[Lookup Approver Group]) && ISBLANK('Invoices'[Lookup Requester Group])
)Shows rows where the Approver or Requester Group matches the signed-in user.
Excludes rows with blank groups (like Jay).
Employees see all invoices for their matching group.
Did it work? ✔ Give a Kudo • Mark as Solution – help others too!
Regards,
Rufyda Rahma | Microsoft MIE
