cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Need understanding for RLS

Hi Guys,

 

I have created the below report and have a role in my mode as user = USERNAME().

 

Now what I want is that whenever a user logs in he/she sees data for only their Company ID and not other companies data.

So can you help me how to set up this?

 

If a make a company filter to my report then I think it will not work as everyone will see the data for the company which i'll select in the filter.

 

Regards,

Himanshu

 

Himanshu_13_0-1602834979490.png

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Himanshu_13 

Build a sample to you to show you how to achieve the goal.

Data Table:

1.png

Build a user table:

2.png

Relating two tables by CompanyID company.

Manage Roles in Data Table.

 [Company ID]=
CALCULATE (
    MAX ( Data[Company ID] ),
    FILTER ( User, User[User] = USERPRINCIPALNAME() )
) 

Let's view as user1@xxxx.com

3.png

You can download the pbix file from this link: Need understanding for RLS

 

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. 

View solution in original post

6 REPLIES 6

@amitchandak I dont have the desired output as I will be a visual.

 

What I want is suppose there are 2 users in my system having different company ID against their name i.e 280, 281.

Is it possible that when user 1 logs in then he sees data for the company 280 only and not for company 281.

 

I'm sharing the pbix file. Can you please help me with this. I want if a certain user logs in then he/she should see only their company ID's data and nothing else.

 

please suggest how to share the pbix file. i cant see option to insert a file.

 

Regards,

Himanshu

@Himanshu_13 for this to work you need to make use of DRLS(Dynamic Row Level Security)

Prerequesties--->Create a table "User Details" and have details about thier UserID,email id ,name, and create relationship between the user table and the dimension table as below ....this will meet your requirement any help please do let me know
DRLS.PNG

Hope it resolves your issue? Please consider accepting it as the solution to help the other members find it more quickly.


Appreciate your Kudos, Press the thumbs up button!!👍



Hi @Himanshu_13 

Build a sample to you to show you how to achieve the goal.

Data Table:

1.png

Build a user table:

2.png

Relating two tables by CompanyID company.

Manage Roles in Data Table.

 [Company ID]=
CALCULATE (
    MAX ( Data[Company ID] ),
    FILTER ( User, User[User] = USERPRINCIPALNAME() )
) 

Let's view as user1@xxxx.com

3.png

You can download the pbix file from this link: Need understanding for RLS

 

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. 

View solution in original post

@RicoZhou Thanks for your help but I have a question.

 

Can you please explain what exactly the below expression is doing. The MAX function is picking what?

The condition is working for me but not completely. I have some roles also defined with users ( see below table)

Now if I select a user name the sales person should see only his sales data and the manager should see complete data.

 

Regards,

Himanshu

 

company IDuserrolereporttoIDreporttoname
280user1@xxx.comSales Person5abc
281user2@xxx.comSales Person10xyz
281user3@xxx.comManager12zzz

 

 [Company ID]=
CALCULATE (
    MAX ( Data[Company ID] ),
    FILTER ( User, User[User] = USERPRINCIPALNAME() )
) 

 

Hi @Himanshu_13 

In my sample there is not role column in it, so I can calculate company ID for each user by this measure.

There is an organization hierachy in your sample, you may refer to this blog: Dynamic Row Level Security with Organizational Hierarchy Power BI

 

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. 

 

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors