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())

     

     

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())