Filtering records based on who logged in

[jasonyeung87]

Hi,

I have a Power BI report which contains a total of various amounts from the opportunity entity (in our CRM). Below is a screenshot of the total amounts of all the opportunities.

Each of these opportunities also has an owner associated with it, which are our organizational accounts. We use the same organizational accounts to log into Power BI Service. The above report is created in Power BI Desktop and then published to Power BI Service. Is there a way to make the total amount be just for the opportunities belonging to the person who logged in? 

For example in the above example, it shows 1.01M for all users. If we have 0.3M opportunies owned by Jason and 0.701M owned by Jack, then when Jason logs in, the above will show 0.3M; while for Jack, it will show 0.701M?

Sincerely,

Jason

[Anonymous · Accepted solution]

Hi @jasonyeung87 ,

GilbertQ  gives the right answer.

Row Level Security (RLS) is the way we restrict data presented in a report based on user login.

The complexity of the RLS set up varies depending on the number of dimensions and dimension members that require security.

If you have a small, fixed list of users and a static number of dimensions, then a basic RLS setup will work. This approach involves creating roles for different user types and then applying filters to the desired dimension tables. 

However, if your user list is longer and your dimensions are subject to changes, you will need the dynamic approach.

 You would go through the following steps:

1.On the ‘Modeling’ tab in the Power BI Desktop toolbar click on “Manage roles”.

2.Create a new role, select the table which you want to get filtered based on the role, and then create a True/False statement to filter this table with.

3.Now that the role has been created test the functionality using the ‘View as’ button.

4.After you have uploaded this report to the Power BI portal, go to the Security settings for the data source to assign Users to the desired roles.

And please notice that RLS will not work if you have edit permission.

For details, you can refer to:How to Setup Static and Dynamic Row Level Security (RLS) in Power BI? 

Hope it helps,

Community Support Team _ Caitlyn

If we have solved your problem, please consider Accept it as the solution to help the other members find it more quickly.

[GilbertQ]

Hi @jasonyeung87 

Yes this can be done using Row level Security

here are details how to get this done Restrict data access with row-level security (RLS) for Power BI Desktop - Power BI | Microsoft Docs