Forum Discussion
dynamic RLS
hello I have this table with the accesses that each user must have.
furthermore i created this rule, however with use the maxx is only the last value is returned
example: [email protected] should have access to "AAABBBCCCEEE" and "AAABBBCCCFFF".
with the current rule he only has access to "AAABBBCCCEEE".
Every help is welcome.
Thanks a lot for the help.
plaese apologize for my English
| access level | Name | Parent | Hierarchy | |
| [email protected] | AAA | AAA | AAA | |
| [email protected] | AAABBB | BBB | AAA | AAA | BBB |
| [email protected] | AAABBBCCC | CCC | BBB | AAA | BBB | CCC |
| [email protected] | AAABBBCCCEEE | EEE | CCC | AAA | BBB | CCC | EEE |
| [email protected] | AAABBBCCCFFF | FFF | CCC | AAA | BBB | CCC | FFF |
| [email protected] | AAABBBCCCHHH | HHH | CCC | AAA | BBB | CCC | HHH |
the rule
PATHCONTAINS(User[Hierarchy],
MaxX(
Filter(
User,
[Email]=USERPRINCIPALNAME()
)
,IF(User[Parent]="",User[Name],User[Name])
)
)
Hi Anonymous ,
As the formula in the link you refer to you should create：
Org Level 4 = LOOKUPVALUE ( User[Email], User[Name], PATHITEM ( 'User'[Hierarchy], 4) ) Org Level 3 = LOOKUPVALUE ( User[Email], User[Name], PATHITEM ( 'User'[Hierarchy], 3) ) Org Level 2 = LOOKUPVALUE ( User[Email], User[Name], PATHITEM ( 'User'[Hierarchy], 2) ) Org Level 1 = LOOKUPVALUE ( User[Email], User[Name], PATHITEM ( 'User'[Hierarchy], 1) )If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
11 Replies
Hi Anonymous ,
I'm doubt why you use MAXX. Please try the followinng dax:
CALCULATE(IF(User[Parent]="",User[Name],User[Name]),Filter(User,[Email]=USERPRINCIPALNAME()))If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
i'm sorry i forgot to put PATHCONTAINS in the rule.
i need maxx because of PATHCONTAINS.
is there any other solution?
Thanks in advance
Anonymous ,
Try RLS
https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-Unleash-row-level-security-patterns-in-Power/td-p/712613
https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi
https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies
https://docs.microsoft.com/en-us/power-bi/service-admin-rls
https://blog.tallan.com/2018/04/10/row-level-security-in-power-bi-part-1-roles-and-users/
https://radacad.com/what-do-you-need-to-implement-dynamic-row-level-security-in-power-bihi amitchandak
Thanks for your help but it didn't work.
i followed this toturial https://radacad.com/dynamic-row-level-security-with-profiles-and-users-in-power-bi but it doesn't work because it doesn't use hierarchy.that is, user [email protected] must see all records. the user [email protected] should see everything below him (and himself) and the user [email protected] should only see himself.
any idea how to do this?
thank you very muchAnonymous , have you checked this one.
https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies
Hi Joaoar,
I had similar requirement and below is how I solved it.
Email Access Level Name Hierarchy Role_Order First_Role Second_Role [email protected] AAABBB BBB AAA | BBB 1 BBB [email protected] AAABBBCCC CCC AAA | BBB | CCC 1 CCC [email protected] AAABBBCCCEEE EEE AAA | BBB | CCC | DDD | EEE 1 EEE [email protected] AAABBBCCCFFF FFF AAA | BBB | CCC | FFF 2 FFF Role_Order: New column using Power Query editior. (Link: https://youtu.be/7CqXdSEN2k4)
First_Role: Calculated column Dax: If(Role_Order = 1,Name).
Second_Role: Calculated column Dax: If(Role_Order = 2,Name).
RLS:
PATHCONTAINS(User[Hierarchy],
Maxx(
Filter(
User,
[Email]=USERPRINCIPALNAME()
)
, User[First_Role]
)
) | |
PATHCONTAINS(User[Hierarchy],
Maxx(
Filter(
User,
[Email]=USERPRINCIPALNAME()
)
, User[Second_Role]
)
)
Thanks.
