RLS multiple usernames

[MIkkelHyldig]

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
Mikkel@x.com7068	Mikkel@x.com	7068
Mikkel@x.com1001	Mikkel@x.com	1001
Mikkel@x.com1002	Mikkel@x.com	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". 

[AllisonKennedy]

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

[MIkkelHyldig]

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 Mikkel@x.com 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: Mikkel@x.com1001, Mikkel@x.com1002 and Mikkel@x.com7068. But I dont know how to handle multiple users in UserPrincipal logic for RLS. 

Does it makes sense? 

Thanks,

[AllisonKennedy]

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

[MIkkelHyldig]

Hi @AllisonKennedy,

Exacatly but UserPrincipalName is Mikkel@x.com and not Mikkel@x.com1001 so I need the RLS code to UserPrincipalName (Mikkel@x.com) = Key Mikkel@x.com1001 (but remove 1001 in RLS logic) so it matches. Again, with UserPrincipalName equal to one store, its no problem. 

Here is my Power Automate flow:

[AllisonKennedy]

@MIkkelHyldig  I think if you update the power automate to use the 'key' column instead of the UPN value that should do the trick? I think... let me know if it works as I haven't used that action in Power Automate so will be interested to know if you can 'cheat' the UPN in that way. 

[MIkkelHyldig]

Hi @AllisonKennedy,

Key doesnt work unless my DAX handles the UserPrincipalName+StoreNo to equal UserPrincipalName. Im working on the issue later this week and will give a solution if found. 

Thanks for the inputs so far!

[AllisonKennedy]

@MIkkelHyldig  You may need to use USERNAME() instead of USERPRINCIPALNAME

For reference for others, I'm using this article to find details of the Power BI action (had to look up what info it wanted for the dataset): Export and email a report with Power Automate - Power BI | Microsoft Learn 

It worked in my test scenario: 

Note that the Identities Username -1 value for dynamic content is using the "Key" column from the database, not the UserPrincipalName. You can use two columns here if you don't have that concatenated in SQL already (I don't have a SQL database in my test tenant so just used manual input to demo). 

Run the flow using inputs of 11001 and 11002 as the UPN-Key values and here's the outputs: 

[MIkkelHyldig]

HI @AllisonKennedy 

Are you running the flow Export to File for PBI in a Apply to each flow or simply getting two seperate emails for 11001 and 11002?  And second, how can the RLS rule customerkey be equal to username? My username is NAME\Mikkel.Hyldig and the Key column im using is mikkel.hyldig@ANOTHERNAME.com? 

I have no issues getting the flow to run at its own with the userprincipalname for one store no at a time but when I wrap a Apply to each action around it keeps looping the userprincipalname for the same Store no 7068. 

[AllisonKennedy]

@MIkkelHyldig  it doesn't matter if the flow logic is run in an apply to each or as two separate flows. The challenge is what you put in the apply to each. 

You have User Principal Name in the flow where you should put the combined 'Key' value:

As you have annotated, your 'security' table includes the 'key' column:

So the output of the 'get rows (V2)' step should be something like:

Username	Store	Key
Mike@abc.com	1	Mike@abc.com1
Mike@abc.com	2	Mike@abc.com2
Jo@abc.com	2	Jo@abc.com2

Inside the 'apply to each', what happens is it repeats the flow for each row of that table. If you put the 'Username' column in the Power BI 'Identities username - 1' field, then it will use 'mike@abc.com' twice. So you need to put the 'Key' column there (even though it's not technically the username). This will force Power Automate to use the Mike@abc.com1 the first time, then Mike@abc.com2 the second time.

Then in Power BI with the DAX, do the same and use the 'Key' column as though it were the username.

Because you want the 'key' column to be passed to Power BI, you can 'cheat' the RLS by using that 'key' column as the username, in both Power Automate and Power BI. 

Does that make sense?