User can see all data - although they have a dataverse security role

[TorgeHH]

I have the following problem:

I have 2 data sources in power BI. One datasource is in direct query mode. I query the account table from dataverse. This table has a relationship with the sales table, which is in import mode. The relationship is the accountid from dataverse.

I use a security role that allows users to only see customers in the account table, that are assigned to their business unit. When I open the power bi report with the less priviliged account, I can only filter for the customers I am allowed to see and I can only see their sales.

High priviliged account:

Low priviliged account:

But when I dont select any customer or choose "select all", I can see the overall amount of sales. This means I can indirectly see the sales of the other customers, although I am not priviliged to do so. 

The 659k are the value of all Test Customers which I can see also with the privliged account.

I there a way to solve this problem, without creating a new security role in power bi ? I only want to use the security roles in dataverse...

[Anonymous]

Hi, @TorgeHH 

If your data is in an import model, you need to define security roles in Power BI.

Row-level security (RLS) with Power BI - Microsoft Fabric | Microsoft Learn

If you don't want to define security roles for Power BI, then you'll need to change your sales table to directquery mode, and when a user opens the report, Power BI sends a query to the underlying data source, which applies security rules to the data based on the user's credentials.

In your case, defining a Power BI security role would be more appropriate than migrating data to dataverse.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[TorgeHH]

Hey, 

thanks for your answer! One more information from my side: The account table is in direct query mode and the security roles work on it. I was expecting due to the fact, that only the customers are shown in the account table and that there is a filter on that table plus the relationship, that only sales from these customers are displayed....

[Anonymous · Accepted solution]

Hi, @TorgeHH 

You can use the USERPRINCIPALNAME method to write a measure on top of what you are. If the current user is at a high level, when he clicks on the slicer "All" option displays the data normally. If it is a low-level user, it returns empty. You can refer to the following tutorial to learn how to use this function:

Reading active Power BI security roles in DAX - SQLBI

Dynamic Row Level Security with Profiles and Users in Power BI : Many-to-Many Relationship - RADACAD

Best Regards

Jianpeng Li

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