topic Manager-level RLS filtering based on "Is Manager" stored in a dimension table in DAX Commands and Tips

Manager-level RLS filtering based on "Is Manager" stored in a dimension table

ITbean — Sat, 01 Feb 2020 22:13:13 GMT

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	Email
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.

Re: Manager-level RLS filtering based on "Is Manager" stored in a dimension table

Anonymous — Sun, 02 Feb 2020 03:44:34 GMT

Please check this post may be it will help you.

https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi

Thanks,
Pravin

If it resolves your problem mark it as a solution and give Kudos.

Re: Manager-level RLS filtering based on "Is Manager" stored in a dimension table

ITbean — Mon, 03 Feb 2020 05:37:34 GMT

I appreciate your reply, but that's one of the articles I've read before posting.

Re: Manager-level RLS filtering based on "Is Manager" stored in a dimension table

Anonymous — Mon, 03 Feb 2020 08:20:04 GMT

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/913784

Re: Manager-level RLS filtering based on "Is Manager" stored in a dimension table

ITbean — Mon, 03 Feb 2020 16:27:47 GMT

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.

topic Manager-level RLS filtering based on &quot;Is Manager&quot; stored in a dimension table in DAX Commands and Tips

Manager-level RLS filtering based on "Is Manager" stored in a dimension table

Re: Manager-level RLS filtering based on "Is Manager" stored in a dimension table

Re: Manager-level RLS filtering based on "Is Manager" stored in a dimension table

Re: Manager-level RLS filtering based on "Is Manager" stored in a dimension table

Re: Manager-level RLS filtering based on "Is Manager" stored in a dimension table

topic Manager-level RLS filtering based on "Is Manager" stored in a dimension table in DAX Commands and Tips