Filtering data for customers with embedded Power BI with a shared data source?

[IanV]

I'm sure this has been answered somewhere, but I'm having a very difficult time finding information about this exact scenario.

Let's assume you have a web application with several customers (each with multiple users), backed by a single relational database. All customer data is in shared tables, segmented by a CustomerId. How, then, would you configure reports so that a user for a particular customer could log in, open an embedded report, and see only data related to their organization?

According to this question (Power BI embedded with user data), it is possible to have Power BI switch actual data sources based on the current user. While this is close, I don't think it solves my problem.

This question was also very close (Issue: How to send the same report to a different client.), but it didn't quite explain how creating a relationship to a client table helps when a particular client is viewing/running a report on their own.

From what I've seen so far, the Power BI tools are incredible. I'd love to integrate/embed it into some applications, but I can't seem to find a clearly defined way around this.

[Eric_Zhang · Accepted solution]

@IanV

Have you checked this article Row level security with Power BI Embedded?

[IanV]

@Eric_Zhang

Actually, ignore my last reply. After looking more into how RLS works with Power BI embedded, I figured out that I could actually pass in a CustomerId as the username on the embed token, and then use DAX with the USERNAME() function to tie that to the CustomerId on the report.

So essentially, in RLS, I have a role "Customer" with the following DAX expression: 

[CustomerId] = USERNAME()

Then, I  created a token with the appropriate role and username:

{
  "wid":"{workspace id}",
  "rid":"{report id}",
  /* other properties */
  "roles":"Customer",
  "username":"my-customer-uid"
}

I'm not sure if this is what you intended when you suggested using RLS, but it should definitely work.

[Anonymous]

Hey @IanV

Can you please tell me where am I supposed to create the token? I'm new at this and I don't understand. I read the post about power BI Embedded but I can't figure out the part with the token.

Please help me..

Thanks

[marco_comande]

https://docs.microsoft.com/it-it/power-bi/developer/embedded/cloud-rls

[IanV]

Yes, I did look at that. From what I could tell, though, it looked like a role would need set up for every customer with every associated user (they'd also need removed as customers' staff turn over). This is fine for a small number of customers, but it doesn't seem like it would be easily maintainable as business grows.

Also, would this need set up on a report-by-report basis? If so, then that compounds the issue of maintenance.

Are there alternatives to managing RLS manually through Power BI desktop? Is role/rule setup available through the SDK?

I'm pretty new to all of this, so let me know if I'm wildly off-base here. I'm also open to utilizing other types of data sources, if there are better solutions. For example, if a report could pass along user info to a REST or OData endpoint, then the data could be filtered server-side before it even makes it to the report itself.