Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

Hi @Anonymous 

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
Anonymous
Not applicable

@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

Hi @Anonymous 

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. 

Anonymous
Not applicable

@v-rzhou-msft 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 @Anonymous 

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. 

 

Anonymous
Not applicable

@Anonymous 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!!👍



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.