Error Exporting Power BI using Power Automate using RLS

[gaurrdentons]

Hi All,

I am trying to create Power BI Subscription logic (with filters and RLS) using Power Automate.
We have Premium Capacity also.

The requirement is to generate separate PDFs for each user based on their FILTERS (maintained in Sharepoint Online list) and RLS (which comes from AAS).

So, the flow loops through the Sharepoint List and generate PDFs and sent them to each user.
I am using "Export to File for Power BI Report" action

To test the flow, I used my credentials as one of the users, having filters and RLS assigned.
The flow runs successfully for me and sends email (since it is executed from my credentials and the Power BI connector is using my credentials).

But it fails for all the users from the Sharepoint list with very known error:
Creating embed token for accessing dataset requires effective identity username to be identical to the caller's principal name.

So, it means the following to me:

• I have to run flow from each user credentials - Not possible
• Tried Service Principal, but the Power BI connector in Power Automate does NOT have an option to login as Service Principal. (Microsoft has provided for CDS/Dataverse)
• Use Custom Connector, gets too complicated and does not work in the end

Please suggest and is there any example to build Power BI Subscription using Power Automate with different filters and RLS applied for each user.

Thanks in advance

Ritesh

[lbendlin]

Assuming you have a suitable RLS table in your data model (that controls the rest of the data model ) with the email as a primary key.  You then set the report level filter to that table and that user email.

[lbendlin]

If you have RLS in place then you can use your ONE report level filter to inject the user emai into the export request. That will then take care of the data visibility.

Be prepared for lots of agony though - exporting multiple reports will result in 429's  very quickly.  It's rather useless at the moment.

[gaurrdentons]

Hi Ibendlin

Thanks for your reply.

In my flow (which uses "Export to File for Power BI Report" action/API ), the Report Filters and RLS are applied correctly and generates PDFs but only for user who executes the flow. For rest of the user,  it shows error message 

Creating embed token for accessing dataset requires effective identity username to be identical to the caller's principal name.

However, I noticed yesterday that this happens only when source system is AAS otherwise it works fine other source systems.

Any suggestions? Should I use CUSTOMDATA?

Regards

Ritesh

[lbendlin]

That's not what I meant. Run the flow with high level permissions but inject the target user email into the RLS table via the report level filter.

[gaurrdentons]

Hi Ibendlin

Apologise but I don't understand when you say "inject in RLS table via report level filter".

Here RLS table sits in AAS.

Please correct me.

Thanks

Ritesh

[v-rzhou-msft]

Hi @gaurrdentons 

I find some offical blogs about export Power BI Report by Power Auto.

For reference: Row-level security in Power Automate

Video: Send Report Email By RLS in Power BI using Power Automate

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gaurrdentons]

Thanks Rico for your link.

I have already these options, and it works fine when "identities UserName" and "user running Power Automate" are the same but it fails when I want to run Power Automate for some other user, who has RLS setup and the source is AAS.

I think solution could be using Service Principal along with CUSTOMDATA, I will try this only the proper tenant-level access is granted to Service Principal.

However, I was looking for some example who has implemented this logic.

Thanks

Ritesh

[lbendlin]

The links provided by @v-rzhou-msft are interesting but a little misleading.  It doesn't send emails as per RLS, but as per Role.  The user identity is the one who renders all the reports, but as that role. It's nice if you have roles defined and want to send the same email to all users within a role,  but it will not work with personalized individual emails as in your case.  In your case you need to leave the role settings alone and manipulate the "Identities Username" dynamically.  I think.

[E__]

Hi,
Could you elaborate some more on how to set the report level filter dynamically in the export connector?

Thanks!

[lbendlin]

[E__]

Thanks. I had already found where to set the report level filters, but I'm specifically interested in how to inject the user email into the export request, as you suggested in an earlier reply. How would this work?

[gaurrdentons]

Hi

The user email, role name and CustomData (if any) should be part of Identities UserName (as per above screen shot.

But, in my case, I have not used the action 'Export to File for Power BI reports", rather I have used the API directly "Export to File in Group" series and passed User email as part of BODY.

Thanks

Ritesh

[lbendlin]

Whatever works for you.  The flow component takes care of a lot of the plumbing that you have to do manually when you do it via API calls (including waiting for the rendering to finish etc).

[gaurrdentons]

The flow component achieves most of the things but it does NOT allow certain things.

My requirement was to generate Power BI reports in PDF based on RLS and should be based on Service Principal rather than a USER account, which generates an Access Token. You cannot pass a Token in the flow component as Authorization but API allows it to pass as part of the Header.

[gaurrdentons]

Hi

Finally, I managed to implement customized Power BI subscription logic using Power Automate, which Service Principal to generate an Access Token and then using Power BI REST API's the output files are generated as per RLS.

Here is the snapshot

😀