Forum Discussion
RLS multiple usernames
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:
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 [email protected]?
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.
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 [email protected] 1 [email protected] [email protected] 2 [email protected] 2 [email protected] 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 '[email protected]' 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 [email protected] the first time, then [email protected] 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?