Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Pierrev31
Frequent Visitor

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. Partner or and Associate Partner, he should be able to see the Engagement he is a part of. 

 Pierrev31_0-1712081835872.png

 

In the following example, Partner B is an Eng Partner for the Engagement 3000431234, so he should see that row. The way I have set up, he can only see the rows his username shows up. However, in this case he would need to be able to see the other Engs. as well because one of the Engagements he is part of is in the Lead Engagement 2001401234.  In cases where neither of these two conditions apply, he should not be able to see either the Eng. nor the Lead Eng. 

 

Lead Engagement    

Engagement     

Lead Eng Partner Email     

Eng Partner Email         

Associate Partner Email

2001401234

3000431234

PartnerA@abcd.com

PartnerB@abcd.com

n/a

2001401234

3000401555

PartnerA@abcd.com

PartnerA@abcd.com

n/a

2001401234

2001401234

PartnerA@abcd.com

PartnerA@abcd.com

n/a

 

And that is where I'm having trouble setting up, because in some cases such as this one, the partner isn't in either of those three email fields, but because he is part of the Lead Engagement, he should also be able to see them as well. 

 

Any help is appreciated! Thanks! 

1 ACCEPTED SOLUTION

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

5 REPLIES 5
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
OwenAuger
Super User
Super User

Hi @Pierrev31 

Just clarifying the requirement:

 

For the 2nd condition, where a Partner can see rows relating to Lead Engagements that he is a part of, does that apply regardless of whether he is Lead Partner, Lead Eng Partner or Associate Partner against the Lead Engagement in question?

 

In other words, is is the rule that if a Partner appears at all in at least one row for a particular Lead Engagement, he should see all rows for that Lead Engagement?

 

Regards

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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?
20014012343000431234PartnerA@abcd.comPartnerB@abcd.comn/aYes
20014012343000401555PartnerA@abcd.comPartnerA@abcd.comn/aYes
20014012342001401234PartnerA@abcd.comPartnerA@abcd.comn/aYes
20075435432007543543PartnerD@abcd.comPartnerF@abcd.comn/aNo
20075435433001546654PartnerD@abcd.comPartnerD@abcd.comn/aNo
20051254522005125452PartnerD@abcd.comPartnerD@abcd.comPartnerB@abcd.comYes
20051254523005324565PartnerD@abcd.comPartnerD@abcd.comn/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! 

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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! 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors