Forum Discussion
Need help creating Row Level Security based on the Department Name in the Table
HR Table (HR Report)
Department | Employee ID | Employee | |
Acct | 111 | A Smith | |
Acct | 222 | B Daks | |
Acct | 333 | C Mark | |
Acct | 444 | D Tim | |
Acct | 555 | E Sam | |
Acct | 666 | F Jack | |
Data | 150 | G Kim | |
Data | 250 | H Tom | |
Data | 350 | I Boy | |
Data | 450 | J May | |
Data | 550 | K Leo | |
Data | 650 | L Pam | |
Data | 750 | M Luke | |
IT | 100 | N Kobe | |
IT | 200 | O Will | |
IT | 300 | P Heal | |
IT | 400 | Q Taz | |
IT | 500 | R Sun | |
IT | 600 | S Craig | |
IT | 700 | T Corey |
Medical Table (Medical Report)
Department | Employee ID | Employee | Medical | |
Acct | 111 | A Smith | Respiratory | |
Acct | 111 | A Smith | TB | |
Acct | 111 | A Smith | Chol | |
Acct | 222 | B Daks | TB | |
Acct | 333 | C Mark | Chol | |
Acct | 444 | D Tim | Typ | |
Acct | 555 | E Sam | Chol | |
Acct | 666 | F Jack | TB | |
Acct | 666 | F Jack | Chol | |
Data | 150 | G Kim | Chol | |
Data | 250 | H Tom | Respiratory | |
Data | 350 | I Boy | TB | |
Data | 450 | J May | Chol | |
Data | 550 | K Leo | Chol | |
Data | 650 | L Pam | Typ | |
Data | 750 | M Luke | Respiratory | |
Data | 750 | M Luke | TB | |
Data | 750 | M Luke | Typ | |
Data | 750 | M Luke | Chol | |
IT | 100 | N Kobe | TB | |
IT | 200 | O Will | TB | |
IT | 300 | P Heal | Typ | |
IT | 400 | Q Taz | Typ | |
IT | 500 | R Sun | Typ | |
IT | 600 | S Craig | Chol | |
IT | 700 | T Corey | Chol |
Mask Table (Mask Report)
Department | Employee ID | Employee | Mask | |
Acct | 111 | A Smith | N95 | |
Acct | 111 | A Smith | Surgical | |
Acct | 111 | A Smith | Basic | |
Acct | 222 | B Daks | N95 | |
Acct | 333 | C Mark | Basic | |
Acct | 444 | D Tim | Basic | |
Acct | 555 | E Sam | Surgical | |
Acct | 666 | F Jack | Basic | |
Acct | 666 | F Jack | Surgical | |
Data | 150 | G Kim | Surgical | |
Data | 250 | H Tom | N95 | |
Data | 350 | I Boy | Surgical | |
Data | 450 | J May | Surgical | |
Data | 550 | K Leo | Surgical | |
Data | 650 | L Pam | Surgical | |
Data | 750 | M Luke | Basic | |
Data | 750 | M Luke | Surgical | |
Data | 750 | M Luke | N95 | |
IT | 100 | N Kobe | Basic | |
IT | 200 | O Will | N95 | |
IT | 300 | P Heal | N95 | |
IT | 400 | Q Taz | Surgical | |
IT | 500 | R Sun | Basic | |
IT | 600 | S Craig | Basic | |
IT | 700 | T Corey | Basic |
- All Tables were imported from Dataverse
- How can I set up row level security in powerBi so that each employee can only see data from their respective department in the table. These employee are not members in the workspace. The report will only be published and shared via powerbi online link
- When “A Smith” in Acct department open/views the HR Report page, he must only see the following record:
Department
Employee ID
Employee
email
Acct
111
A Smith
Acct
222
B Daks
Acct
333
C Mark
Acct
444
D Tim
Acct
555
E Sam
Acct
666
F Jack
Hi Jadegirlify ,
For this you would need to have a formula in the department column that would get the department for the current login something similar to:
var selecteduserdepartment = MAXX(FILTER(Users, Users[email] = USERPRINCIPALNAME()), Users[Department]) RETURN Users[Department] = selecteduserdepartmentThis will allow you to do the filter you need:
This need to be applied to the user table:
My only question is what do you mean when you say that "These employee are not members in the workspace. The report will only be published and shared via powerbi online link"?
What is the Power BI Online Link? Is it the Share to Web link? that this is not a secure link and RLS will not work on top of it?
RLS only works within the service and can be trough a workspace or giving acces to an app.
1 Reply
Hi Jadegirlify ,
For this you would need to have a formula in the department column that would get the department for the current login something similar to:
var selecteduserdepartment = MAXX(FILTER(Users, Users[email] = USERPRINCIPALNAME()), Users[Department]) RETURN Users[Department] = selecteduserdepartmentThis will allow you to do the filter you need:
This need to be applied to the user table:
My only question is what do you mean when you say that "These employee are not members in the workspace. The report will only be published and shared via powerbi online link"?
What is the Power BI Online Link? Is it the Share to Web link? that this is not a secure link and RLS will not work on top of it?
RLS only works within the service and can be trough a workspace or giving acces to an app.