Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
Hello!
I want to implement RLS from a hierarchy path calculated by the path function. Hierarchy is a column calculated with the path function and OrgID is the child node an dcontains letters and numbers.
this is my DAX code in the Dax editor for RLS.
PATHCONTAINS(
Accesstree[Hierarchy],
MaxX(
Filter(
Accesstree,
[Email] = USERPRINCIPALNAME()
),
Accesstree[OrgID]
)
)
The problem here is when I assign this role and log in with an email, it misses some parts because people can have an email linked to several OrgIDs, and then I only see the OrgID that has the highest value because of MaxX function. How can I rewrite the code to fix this? When peolpe log in, they should be able to see all of the rows with their own OrgID and the ones that are under them in the hierarchy/path.
Solved! Go to Solution.
Hi there @Lebombaa
Something like this should do the trick:
VAR UPN = USERPRINCIPALNAME()
VAR CurrentUserOrgID =
CALCULATETABLE (
SELECTCOLUMNS ( VALUES ( Accesstree[OrgID] ), "@CurrentUserOrgID", Accesstree[OrgID] ),
Accesstree[Email] = UPN,
REMOVEFILTERS ()
)
VAR CurrentHierarchy =
Accesstree[Hierarchy]
RETURN
NOT ISEMPTY (
FILTER (
CurrentUserOrgID,
PATHCONTAINS(
CurrentHierarchy,
[@CurrentUserOrgID]
)
)
)
The basic idea is that CurrentUserOrgID is a table of all OrgID values for the current user. Then we check whether the current row's path contains any of those, and if so return TRUE, otherwise FALSE.
Does this work for you?
Hi there @Lebombaa
Something like this should do the trick:
VAR UPN = USERPRINCIPALNAME()
VAR CurrentUserOrgID =
CALCULATETABLE (
SELECTCOLUMNS ( VALUES ( Accesstree[OrgID] ), "@CurrentUserOrgID", Accesstree[OrgID] ),
Accesstree[Email] = UPN,
REMOVEFILTERS ()
)
VAR CurrentHierarchy =
Accesstree[Hierarchy]
RETURN
NOT ISEMPTY (
FILTER (
CurrentUserOrgID,
PATHCONTAINS(
CurrentHierarchy,
[@CurrentUserOrgID]
)
)
)
The basic idea is that CurrentUserOrgID is a table of all OrgID values for the current user. Then we check whether the current row's path contains any of those, and if so return TRUE, otherwise FALSE.
Does this work for you?
Yes it worked thank you!
User | Count |
---|---|
13 | |
12 | |
8 | |
8 | |
6 |
User | Count |
---|---|
28 | |
19 | |
13 | |
11 | |
7 |