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
