cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fabian_Z
Frequent Visitor

Hierarchical Row Level Security with Recursive Lookup

Hello all,

 

in my data model, I am using dynamic row level security in a recursive hierarchy.  It checks if the user has access to the organization or any parent organization and, if yes, shows the data record. Unfortunately, I cannot go beyond five levels (formula length limit) but need seven.  Is it possible to make loop, recursion or list reduction in the formula, so that it just checks the whole path, regardless of the number of levels?

 

This is my DAX-Formula at the moment:

 

OR(OR(OR(OR(
PATHITEMREVERSE(RELATED(Organizations[Org Path]), 1, integer) in calculatetable(values('Authorizations'[ID]), 'Authorizations'[Email]=userprincipalname()),
PATHITEMREVERSE(RELATED(Organizations[Org Path]), 2, integer) in calculatetable(values('Authorizations'[ID]), 'Authorizations'[Email]=userprincipalname())),
PATHITEMREVERSE(RELATED(Organizations[Org Path]), 3, integer) in calculatetable(values('Authorizations'[ID]), 'Authorizations'[Email]=userprincipalname())),
PATHITEMREVERSE(RELATED(Organizations[Org Path]), 4, integer) in calculatetable(values('Authorizations'[ID]), 'Authorizations'[Email]=userprincipalname())),
PATHITEMREVERSE(RELATED(Organizations[Org Path]), 5, integer) in calculatetable(values('Authorizations'[ID]), 'Authorizations'[Email]=userprincipalname()))

 

I imagine something like this (mixture of JavaScript and DAX):

 

[calculatetable(values('Authorizations'[ID]), 'Authorizations'[Email]=userprincipalname())].reduce((found, nextId) -> OR(PATHCONTAINS("path", nextId), FALSE)

 

Regards,

Fabian

0 REPLIES 0

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors