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.
I've read about manager-level RLS where the "Is Manager" field is a column in the employee or user table, but I'd like to implement this security where the "Is Manager" field is in a table that lists the level or pay grade of the employees with a column that defines if that level is a manager or not.
My data model (a simplified version of my real one):
Data_TimeWorked
ID | Date | Duration | CustomerID | EmployeeID |
1 | 1/1/2020 | 2 | 101 | 1 |
2 | 1/1/2020 | 2 | 201 | 2 |
3 | 1/1/2020 | 2 | 225 | 3 |
4 | 1/1/2020 | 2 | 235 | 4 |
5 | 1/1/2020 | 2 | 110 | 5 |
6 | 1/1/2020 | 2 | 116 | 5 |
Employee
ID | Name | PayGrade_ID | |
1 | Son Beverlin | 1 | son.beverlin@constoso.com |
2 | Cedric Brightwell | 2 | cedric.brightwell@constoso.com |
3 | Deonna Borgman | 3 | deonna.borgman@constoso.com |
4 | Laveta Hemstreet | 4 | laveta.hemstreet@constoso.com |
Dim_PayGrade
ID | Level | Description | Is Manager |
1 | 10 | Leader | 1 |
2 | 7 | Manager | 1 |
3 | 5 | Supervisor | 0 |
4 | 3 | Staff | 0 |
5 | 1 | Minion | 0 |
There are 1:many relationships from 'Employee'[ID] to 'Data_TimeWorked'[EmployeeID] and
from 'Dim_PayGrade'[ID] to 'Employee'[PayGrade_ID]
What I seek to do is provide anyone with Manager or above level access to all the data in Data_TimeWorked but for those below Manager to have access only to their own data.
I have tried implementing dynamic RLS with a DAX filter on the Employee table, but so far I'm finding I am lacking in my grasp of DAX filtering, as I can't get syntax that works or I'm trying to apply it to the wrong place.
I tried this but couldn't get the syntax to check out:
If(
MaxX(
Filter(
'Employee','Employee'[Email]=UserPrincipalName())
,RELATED('Dim_PayGrade'[Is Manager])=0,
'Employee'[Email]=UserPrincipalName(),
1=1
)
Also tried:
IF(
LOOKUPVALUE(
'Dim_PayGrade'[Is Manager],
'Employee'[Email],
USERPRINCIPALNAME()
)=0,
[Email] = UserPrincipalName(),
True()
)
I am relatively new to Power BI by the total amount of time I've spent working with it and Power Pivot, but I feel kind of stuck on this issue and I think I'm missing something relatively fundamental, but can't figure out what.
A big thanks to anyone who can help me with this.
I appreciate your reply, but that's one of the articles I've read before posting.
path function and path item is missing in your case . please check post one more time.
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
check my blog here
https://community.powerbi.com/t5/Community-Blog/Connecting-to-a-Tabular-Model-Using-Power-BI/ba-p/91...
I am trying to do Manager-level access as explained here, but with a difference in where I store the "Is Manager" value.
https://radacad.com/dynamic-row-level-security-with-manager-level-access-in-power-bi
I'm not sure how the path applied to my use case, as I am not track WHO manages WHO, but rather just whether a user is a manager or not. Our organization does not have definitive chain of command, as our staff are pooled.
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.
User | Count |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |