Forum Discussion

Bian's avatar
Bian
Helper II
7 years ago
Solved

Different RLS criterias on different sheets

I have a Dynamics 365 Dataset working with cases. The PowerBI reports shows Support data from Cases and Activitys. Page 1 shows cases where a system User in my Business Unit has created an activity....
Need Help
  • Bian's avatar
    Bian
    7 years ago

    Finaly managed to solve this problem!

     

    I created a new merged query in PowerQuery with all activityID and IncidentID.

    Added one column for owner of Incident

    Added one column for owner of Activity

     

    Set relationship from Incident->Securitytable->Activity

     

    Created a RLS role for this new Table:

     

    ('Security CaseActivity'[BusinessUnitIncident] = 'Affärsenhet'[Min affärsenhet])
    ||
    ('Security CaseActivity'[BusinessUnitActivity] = 'Affärsenhet'[Min affärsenhet])

  • Bian's avatar
    Bian
    7 years ago

    The saga continues. The soloution was working but was way to slow in production.

    So now I have another new working solution that is fast!

     

    Ended up merging my Incidents and Incident releted Activitys. So now I only have 1 fact table to work with. Can thereby create a Row level security Role using UserPinciplenamn() and two filters looking for Business Unit on Incident OR Business Unit on Acitivity.

    -----

    [Businessunit Incident] = [My Business Unit]
    ||
    [Businessunit Activity] = [My Business Unit]

    -----

     

    [My Business Unit] is a measure on Dimension Business unit: 

    = LOOKUPVALUE('SystemUser'[businessunitid];'SystemUser'[internalemailaddress];USERPRINCIPALNAME();'SystemUser'[isdisabled];False())

     

     

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

Hi Bian ,

 

That means you need to adjust the conditions. If the conditions conflict with each other, I'm afraid the RLS could not be the solution.

BTW, can you share the details of the conditions?

 

 

Best Regards,

Bian's avatar
Bian
Helper II
7 years ago

Finaly managed to solve this problem!

 

I created a new merged query in PowerQuery with all activityID and IncidentID.

Added one column for owner of Incident

Added one column for owner of Activity

 

Set relationship from Incident->Securitytable->Activity

 

Created a RLS role for this new Table:

 

('Security CaseActivity'[BusinessUnitIncident] = 'Affärsenhet'[Min affärsenhet])
||
('Security CaseActivity'[BusinessUnitActivity] = 'Affärsenhet'[Min affärsenhet])

  • Bian's avatar
    Bian
    Helper II
    7 years ago

    The saga continues. The soloution was working but was way to slow in production.

    So now I have another new working solution that is fast!

     

    Ended up merging my Incidents and Incident releted Activitys. So now I only have 1 fact table to work with. Can thereby create a Row level security Role using UserPinciplenamn() and two filters looking for Business Unit on Incident OR Business Unit on Acitivity.

    -----

    [Businessunit Incident] = [My Business Unit]
    ||
    [Businessunit Activity] = [My Business Unit]

    -----

     

    [My Business Unit] is a measure on Dimension Business unit: 

    = LOOKUPVALUE('SystemUser'[businessunitid];'SystemUser'[internalemailaddress];USERPRINCIPALNAME();'SystemUser'[isdisabled];False())