Level of Security by 3 role

[Bali21]

Hello Every One,

I want to assign 3 level of security to my dashboard where I have more than 50k user.

1. People in Admin Coulmn can view all the data.

2.People in Manager 1 view are restricted only their data. They are not allowed to see anyone else numbers.

3. People in Manager 2 view are restricted only their data. They are not allowed to see anyone else numbers.

4. Can I add Proxy for any manager and they are allow to see their data along with Manager.

Note: I have more than 10K unique manager and cant assign role manually.

Any help how I can implement this in power BI. Small set of data are below.

EMP ID	Name	Location	Admin	Manager 1	Manager 2
1	AB	Del	John	Wade	Issac
2	CD	Kol	Kevin	dave	Aaron
3	EF	Bom	Robert	Seth	Andrew
4	GH	Blr	Brian	Ivan	David
5	IJ	Chn	Crish	Dab	Evan
6	CD	Bom	Beth	Jorge	Gavin
7	GH	Del	John	Wade	Issac
8	GH	Kol	Kevin	dave	Aaron
9	IJ	Bom	Robert	Seth	Andrew
10	AB	Del	Brian	Ivan	David

Thanks in advance!!

[v-yueyunzh-msft · Accepted solution]

Hi , @Bali21 

Thanks for your sample data first!

According to your description, you want to " assign 3 level of security".

Here are the steps you can refer to :
(1)My test data is the same as yours.

(2)We can put this in the "Roles":

var _username= USERNAME()
var _admin =  ALL('Table'[Admin])
var _m1 = ALL('Table'[Manager 1])
var _m2 = ALL('Table'[Manager 2])
var _m1_cur = [Manager 1]
var _m2_cur  = [Manager 2]
return
IF(_username in _admin , True() , IF( _username in _m1 &&  _username = _m1_cur ,True()  , IF( _username in _m2 &&  _username = _m2_cur ,True() ,False()))) 

(2)Then we can test in the "View Roles":

For more information, you can refer to :
Dynamic Row Level Security with Power BI Made Simple - RADACAD

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[Bali21]

@v-yueyunzh-msft :

Thank for the solution, Though I have used Path function to resolve above mention issue!!

I am facing yet another issue😬

I have two data source and I want to use single filter for both the data.Not able to figure how to acheive it.

Table 1:

EMP name	Loc ID	Manager 3	Manager 2	Manager1
Rezza	33	John	Wade	Issac
Mark	33	John	Wade	Issac
Amy	22	John	Wade	Issac
Issac	33	John	Wade
Wade	77	John
Dab	444	Leena
Evan	333	Leena	Dab
lylla	111	Leena	Dab	Evan
Ray	111	Leena	Dab	Evan

Table2:

Location Owner	Loc ID
Issac	33
Issac	22
Issac	44
Wade	77
Wade	88
John	55
John	66
John	99
Evan	111
Evan	222
Evan	555

ScreenShot of PBI without filter:

Once filter is applied Viz 1 not changing, it only applicable to table viz. Could you please suggest how it can be achived. So If I select "Issac" then both viz should be changes as per selection.

Thank in Advance!

[Bali21]

@amitchandak : I have got the heirarcical RLS and implemented the same.

Not sure how I can get for Admin role where user can view all the data and for the proxy role - Role assign to some individual on behalf of manager.