Forum Discussion
RLS multiple usernames
Hi all,
I have two tables with a both ways security connection (1-* with Store column as key), where I want to setup a new RLS logic / rule. The rule should filter so the UserPrincipalName can only see stores Using Key and not UserPrincipalName. I need to use the Key row because I need it to be evaluated in a RLS-step in Power Automate.
1) RLS
| Key | UserPrincipalName | Store |
| [email protected] | [email protected] | 7068 |
| [email protected] | [email protected] | 1001 |
| [email protected] | [email protected] | 1002 |
And 2) Stores
| Stores |
| 7068 |
| 1001 |
| 1002 |
Currntly my RLS Table filter dax measure is:
LEFT('PartnerRLS'[Key], FIND(CONCATENATEX(VALUES('PartnerRLS'[Key]), {"1","2","3","4","5","6","7","8","9"}, ",")
, 'PartnerRLS'[Key]) - 1) = UserPrincipalName()
But its returning an error "a table multiblae values were returned where a single value was expected".
9 Replies
MIkkelHyldig If you're using the UserPrincipalName function, you can just use the UPN column and filtering on that column will propagate to the rest of your data. Not sure what Power Automate functionality you're trying to unlock that's making that not work - can you elaborate?
Hi AllisonKennedy ,
Thanks for your answer.
In Power automate I need to provide a Identity Username in RLS Role Item in action "export to file for PBI reports" and it only seems to work when one Username has one Store. However when a user has multiple stores and needs an exported report for each store I need unique users hence using a new Key-column for RLS. Now, the user [email protected] gets a report for store 7068 three times instead of 7068, then 1001 and then 1002 in three different exported reports.
Thats why I wanted to create, in this example, three unique rows for each store by using three different users: M[email protected], [email protected] and [email protected]. But I dont know how to handle multiple users in UserPrincipal logic for RLS.
Does it makes sense?
Thanks,I haven't used that action in Power Automate but am looking at it now:
Assuming you can provide the 'identities username' value into PowerAutomate that matches the key, the RLS DAX should be simple
Table[Key] = UserPrincipalName()If I'm undestanding correctly??
