Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
Solved! Go to 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
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
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!
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.