Forum Discussion

Pierrev31's avatar
Pierrev31
Frequent Visitor
2 years ago
Solved

Need help setting up Role using USERPRINCIPALNAME() with an extra parameter

Hello everyone!    I created a Role where a Partner could only see their own Engagements by using the USERPRINCIPALNAME() measure.    It is as follows: If he is a Lead Eng. Partner or and Eng. P...
Assigning Roles
Dynamic Role
Filter based on Roles
Security roles
USERPRINCIPALNAME
  • OwenAuger's avatar
    OwenAuger
    2 years ago

    Thanks Pierrev31 

    Yes that clarifies it!

     

    Here is how I would write the RLS filter expression assuming the table is named Engagements:

     

    VAR UPN =
    USERPRINCIPALNAME ()
RETURN
    CALCULATE (
        NOT ISEMPTY ( Engagements ),
        Engagements[Lead Eng Partner Email] = UPN
            || Engagements[Eng Partner Email]  = UPN
            || Engagements[Associate Partner Email] = UPN,
        ALLEXCEPT ( Engagements, Engagements[Lead Engagement] )
    )

     

    We only need to check the 2nd condition since it dominates the 1st condition. That is, if the current user exists on at least one row with the current row's Lead Engagement, then that user exists on the current row.

     

    Mock-up PBIX attached.

     

    Regards

OwenAuger's avatar
OwenAuger
Super User
2 years ago

Glad to hear it 🙂

In the RLS filter expression, CALCULATE adds the values in the current row of Engagements as a filter.

The modifier

ALLEXCEPT ( Engagements, Engagements[Lead Engagement] )

removes all filters from the Engagements except Engagements[Lead Engagement].

In other words, it keeps the just Lead Engagement filter from the current row, ignoring all other values on the current row.

The overall CALCULATE expression then determines whether any rows exist for that Lead Engagement containing the current user.

 

Some discussion of the function in this article:

https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

 

Regards