Row Level Security in PowerBI service cannot filter automatically by user

[dobregon]

Hi,

I have checked this link but i can't have the row level security in my powerbi service workspace.

https://docs.microsoft.com/en-us/power-bi/service-admin-rls

As an example i have 2 tables in datasets

- Markets: Have all the markets by customer id

- Customer: A table by CustomerID and CustomerEmail ie: "zara@customer.com"

So, that I want is that when the customer zara@customer.com logged into PowerBI service and check the report, he can see only his markets.

For doing that i have create a role in PowerBI Desktop as in the URL that do a filter using CustomerEmail = userprincipalname().

Then i have created a filter in markets that if they can see the customeremail in table customer.. show the markets. 

The problem is when i upload the powerbi desktop to the service, i create the role in service and try viewing as role.... the system is ok but when i tried to access to powerbi service using the customeremail account and check the report.. i see everthing, so it is not automatic.

Can you help me on that?

[v-frfei-msft]

Hi @dobregon ,

In your scenario, did you create UPN mappings? Please refer to the video to check the steps.

https://powerbi.microsoft.com/en-us/blog/tech-tip-thursday-user-principal-name-upn-mapping-in-power-...

Regards,

Frank

[dobregon]

@v-frfei-msft  thanks a lot for the reply!

The problem is that in this solution you are creating a gateway to the database using the userprincipalname (email for logging app powerbi); the problem is that i can't have access to all the users that i will have to my database system. For that i have some tables and views in database that are general but related to a uniq table that have the row security level (i.e customer zara can see only the markets from zara in all the world in the general view from sales market)

I have tried some things and maybe you can confirm that. 

1. I have 2 tables (table general sales with market, customer, sales and the table row level security that specify the markets that a customer can see and the emaillogin from each customer).
2. I have done a relationship btween the customerid from table general and tabler users 
   
   
3. I have created a column in table general like (if you can find the customerid in table users put 1 and if not 0) and then i have put the filter as report filter =1. 
   
   
4. I have created the row security level that filter de table users column emailaccess using userprincipalname(). So when someone log to the powerbi, automatically the table user will be filtered by the email showing only the markets available in table users for that email and then the column filter in table general will put 1s only for the markets available and the powerbi visuals will show only that markets (of coruse the filter will be blocked and hidden in one because nobody should can change.
   
   
5. I have tried using "view as a role" in powerbi desktop and it is ok, great, I'm happy with that jajaj

The problem is when I upload the report to the PowerBi Service:

1. I have done the security level in the dataset in the powerbiservice, adding the user that will use the report.
2. If I tried to see as "role" and it works fine, but here we have the problems:

This report is uploaded in a workspace for all my company and then i have created an APP for some reports inside that workspace and shared with the customers and here i have doubts.

I think that all the people added in the workspace (all my company) seems to have an admin user in the role becasue than see everthing but if I remove a person from the workspace and share the report when he access they can see the markets for that user. I have tried to modify the acces from people in my company in the workspace as a member or contributor but they can see everthing.

I have the doubt that it is correct and all of my company can see everthing (that i thing it is a good idea) and the people outside my company that it is added only to the app and not in the workspace can see their row security level.

[jmarek31]

If you are using the new workspace microsoft has stated below.

To enforce row-level security (RLS) for Power BI Pro users browsing content in a workspace, continue to use classic workspaces. Select the Members can only view Power BI content option. Alternatively, publish an Power BI app to those users, or use sharing to distribute content. The forthcoming Viewer Role will enable this scenario in future in new workspace experience workspaces.

So if you want to use the new workspace you need to remove their access to the workspace until the reader role becomes available. Otherwise you would have to use the old workspace.