Role Level Security Dax Measure - looking at different fields to determine access

[amarquiss]

I have a security table that indicates what level a person can access data.  I am trying to build the Dax filter in the Roles to make it apply secuirty at differnt levels. Below I got it working to look at department or specialty but I am stuck how to write this for RPT_GRP_2 and NPI levels. Total newbie to Power bi and Dax so if their is better way to write this metric I am open to suggestions 

VAR Specialty =
CALCULATETABLE (
VALUES (SecurityAccess[SPECIALTY]),
SecurityAccess[USER EMAIL] = USERPRINCIPALNAME()
)
VAR DEPT_ID =
CALCULATETABLE (
VALUES ( SecurityAccess[DEPT_ID] ),
SecurityAccess[USER EMAIL] = USERPRINCIPALNAME()
)
RETURN
AND (
OR (
"ALL" IN DEPT_ID,
DimDeptClarity[Department Id] IN DEPT_ID
),
OR (
"ALL" IN Specialty,
DimDeptClarity[Specialty] IN Specialty
)
)

Example of Security Table

USER EMAIL	USER	AREA	LEVEL	DEPT_ID	SPECIALTY	RPT_GRP_TWO	NPI_ID
executive@childrensomaha.org	Executive	EXEC	All	ALL	ALL	ALL
Division Chief@childrensomaha.org	Division Chief	CP	Specialty	ALL	Primary Care	ALL
Director@childrensomaha.org	Director	CP	Mulitiple Specialties	ALL	ALL	Ambulatory
Manager@childrensomaha.org	Manager	CP	Clinic	1005019	ALL	ALL
Provider@childrensomaha.org	Provider	CP	Provider	ALL	ALL	ALL	12345678

[amarquiss]

That did not work either for our table structure. I got it working with the nested and ...I need to work on adding in NPI still. 

VAR RPT_GRP_TWO =
CALCULATETABLE (
VALUES (SecurityAccess[RPT_GRP_TWO]),
SecurityAccess[USER EMAIL] = USERPRINCIPALNAME()
)

VAR Specialty =
CALCULATETABLE (
VALUES (SecurityAccess[SPECIALTY]),
SecurityAccess[USER EMAIL] = USERPRINCIPALNAME()
)
VAR DEPT_ID =
CALCULATETABLE (
VALUES ( SecurityAccess[DEPT_ID] ),
SecurityAccess[USER EMAIL] = USERPRINCIPALNAME()
)
RETURN
AND (
OR (
"ALL" IN RPT_GRP_TWO,
DimDeptClarity[Area] IN RPT_GRP_TWO
),

(AND (
OR (
"ALL" IN DEPT_ID,
DimDeptClarity[Department Id] IN DEPT_ID
),
OR (
"ALL" IN Specialty,
DimDeptClarity[Specialty] IN Specialty
)
))
)

[amarquiss]

Unfourtnaly we have 4 levels not 1. Provider can only see their own data (NPI), clinic manager can only see dept assigned, director need to see multiple specialties (rolls up 10+ dept into 1 speciality) and RPT_GRP_2  it a level above the specialties that groups specialties for higher up levels. That is why I am trying to find a way to evaluate more then 2 things in my logic. That code I have works amazing for department and speciality I just dont know how to exand to look a more than 2 things. 

[v-rzhou-msft]

Hi @amarquiss ,

If there are same logic in level RPT_GRP_TWO and NPI_ID, you can try the code as below.

VAR Specialty =
CALCULATETABLE (
VALUES (SecurityAccess[SPECIALTY]),
SecurityAccess[USER EMAIL] = USERPRINCIPALNAME()
)
VAR DEPT_ID =
CALCULATETABLE (
VALUES ( SecurityAccess[DEPT_ID] ),
SecurityAccess[USER EMAIL] = USERPRINCIPALNAME()
)
VAR RPT_GRP_TWO = 
CALCULATETABLE (
VALUES ( SecurityAccess[RPT_GRP_TWO] ),
SecurityAccess[USER EMAIL] = USERPRINCIPALNAME()
)
VAR NPI_ID = 
CALCULATETABLE (
VALUES ( SecurityAccess[NPI_ID] ),
SecurityAccess[USER EMAIL] = USERPRINCIPALNAME()
)
RETURN
OR (
"ALL" IN DEPT_ID,
DimDeptClarity[Department Id] IN DEPT_ID
)
&&
OR (
"ALL" IN Specialty,
DimDeptClarity[Specialty] IN Specialty
)
&&
OR (
"ALL" IN RPT_GRP_TWO,
DimDeptClarity[RPT_GRP_TWO] IN RPT_GRP_TWO
)
&&
OR (
"ALL" IN NPI_ID,
DimDeptClarity[NPI_ID] IN NPI_ID
)

It seems that you want to build dynamic RLS with hierarchy level. You may refer to this blog to learn more details.

For reference: Dynamic Row Level Security with Organizational Hierarchy Power BI

Best Regards,
Rico Zhou

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

[Whitewater100]

Hi:

I beleive your security table would have multiple rows for eah person who will view more than one level or Department ID. If you are filtering by Dept ID, the person who see ALL would have her/his name repeated once/ Dept ID.

I will note an example for data I'm more familiar with where the Sales Territory key is driving who see's what. In this scenario

The Filter statement on DeskTop for creating this (Create Roles) is named Employees by Sales Territory :

[SalesTerritoryKey] =
LOOKUPVALUE(
'Security'[Sales Territory ID],
'Security'[Login ID_AD], USERNAME(),
'Security'[Sales Territory ID],
'Sales Territory'[SalesTerritoryKey])

I hope this is helpful.