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

Pierrev31's avatar
Pierrev31
Frequent Visitor
2 years ago

Hello! 

Yes If he appears at all as a Lead Eng Partner, Eng Partner or Associate Partner for any Engagement, he should be able to see all rows associated with the Lead Engagement of that Engagement. So just to make it clear, I expanded the base to show what I mean: 

Lead Engagement      Engagement              Lead Eng Partner       Email                                  Eng Partner Email                    Associate Partner Email           Can see?
20014012343000431234[email protected][email protected]n/aYes
20014012343000401555[email protected][email protected]n/aYes
20014012342001401234[email protected][email protected]n/aYes
20075435432007543543[email protected][email protected]n/aNo
20075435433001546654[email protected][email protected]n/aNo
20051254522005125452[email protected][email protected][email protected]Yes
20051254523005324565[email protected][email protected]n/aYes

 

He is part of the Engagement 3000431234, part of the larger Lead Engagement 2001401234, so he should also be able to the rows for the Eng. 3000401555 and Eng, 2001401234, even though he doesn't show up in any of those email columns for those two Engagements, as they are part of the larger Lead Engagement 2001401234. 

Same thing applies to the Lead Eng. 2005125452, as he is an Associate in the Eng. row 2005125452, so he should the entire Lead Eng. 

And in the case of the Lead Eng. 2007543543, as he doesn't show up in any rows for the Engs. he shouldn't be able to see it at all. 

 

Thank you very much! Hope I was clear! 

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
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

  • Pierrev31's avatar
    Pierrev31
    Frequent Visitor
    2 years ago

    Hello! I think it worked! I'm testing right now, but so far it's perfect! If possible, could you explain how the ALLEXCEPT function worked here to do this? It would be great in case I face a similar problem in the future. Thank you very much!