RLS in Opportunity and Account (CRM)

[rommel20]

Hi Team,

I need to filter Data in Opportunity using RLS based on its role.

user: tesuser1

Business Unit :  Group1

RLS : domainname = username()

*Group1 may contain more than 1 user

Requirement: (need to satisfy both requirements)

Opportunity Data where business unit is business unit log in user

Opportunity Data where Parent Account Owner is login user

I only created the first requirement using

       SystemUser --> Business Unit (get business unit) ---> Opportunity

My problem is 2nd Requirement because in RLS, Union and Merge is not working (regardless who is the user)

*already tried

 SystemUser --> Business Unit (get business unit) ---> Opportunity (Id) 

 SystemUser --> Account ---> Opportunity (Id), then merge both or new table then UNION

Please advice.

 

[rommel20 · Accepted solution]

@v-shex-msft

I was able to resolved the problem using  Manage Role, which create another problem.

Level 1: see all data (no problem)

Level 2:  Same with Level 3 but Business Unit will get the Parent and Child

Level 3: Resolve (below)

[rommel20]

I think you misunderstand my question, because in that relationship I only got business unit to filter Opportunity but Im missing Opportunity Parent  Account Owner is login user.

Currently What Im getting;

       Opportunity where Business Unit is Business Unit of Login User.

What I need;

       Opportunity where Business Unit is Business Unit of Login User  +

       Oppurtunity where Parent Account Owner is Login User 

[v-shex-msft]

Hi @rommel20,

So when current user is the manager, you need to load current data and child data, right?

If this is a case, you can take a look at below sample:

Sample tables.

RLS formula: if current user is sales manager, filter on sales manager column, else filter on salesman column

if(
ISERROR(SEARCH("SalesManager", [Role]))=FALSE(),
[SalesManager]=LOOKUPVALUE(Sheet2[User],Sheet2[Login],USERNAME())
,
[SalesMan]=LOOKUPVALUE(Sheet2[User],Sheet2[Login],USERNAME())
)

Result:

Notice: based on setting, I am the 'salesManager1', so I will get all records of salemanger1.

For you scenario, you need to use 'or' to link two part of formulas to filter both 'user' column and 'parent' column.(If current table not contains parent column, you should add it first)

Sample:

OR([User]=LOOKUPVALUE(Sheet2[User],Sheet2[Login],USERNAME()),
[Parent]=LOOKUPVALUE(Sheet2[User],Sheet2[Login],USERNAME()))

Regards,

Xiaoxin Sheng

[Rubenvw]

Hi,

I have created several reports using similar security models derived from CRM.
To accomplish your requirement I have used another approach which works fine.

I always try to create a table that is called secUserOpportunity. This table has two (or more to make it readable) columns: UserEmail (WindowsLiveID)and OpportunityId. Each row shows the user and the opportunity that he is allowed to see. In your case, this could consist duplicate rows. Next thing you have to do is creating a relationship between this table and the opportunity table. The DAX filter on secUserOpportunity table would be UserEmail = DAXUsername.

Using Join- and Union functions within Power Query (or in SQL I would recommend) should be do the trock to shape data from the systemuser, opportunity and businessunit enitity into the secUserOpportunity table.

If you need more help, please let me know.

[rommel20]

@Rubenvw , I am using CRM as a source, I dont understand how you shape  data of system user, opportunity and businessunit?, how can include record with Parent Account owner?

[rommel20]

Yes, because in that relationship I only got business unit which filter for Opportunity Data. I am missing record where Parent Account Owner is login user.

[v-shex-msft]

HI @rommel20,

Maybe you can turn on the security filter option and try again.

Regards,

Xiaoxin Sheng

[rommel20]

@v-shex-msft and @Seth_C_Bauer 

regardless if I use both direction in system user and business unit, Im still getting the  1st requirements. 

[v-shex-msft]

HI @rommel20,

If you modify all bi-filter options of below relationships , does this issue still appears?

Regards,

Xiaoxin Sheng

[v-shex-msft]

Hi @rommel20,

As Eno1978 said, I'd like to suggest you can modify your bi filter direction option and try again.('single' filter option only works on one side)

In addtion, I think you should use distinct function to make sure this table not contians dulipate records.

Regards,
Xiaoxin Sheng.

[Seth_C_Bauer]

@rommel20 Have you tried making the relationship between businessunits and systemsusers bi-directional?