Different RLS criterias on different sheets

[Bian]

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.

Page 2 show cases on customers owned by my business Unit.

Tried to solve this with Row Level Security but I now belive its impossible to have this dynamic filter. Might be forced to use 2 reports or put a manual filter on the Pages instead.

Have anyone solved this problem?

(I'm aware that RLS per Page is a requested feature not yet implemented)

[Bian · Accepted solution]

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

[v-jiascu-msft]

Hi @Bian ,

Is it that only the users in the Business Unit can see the customers related to that Business Unit? If so, there should be proper relationships. Then you could create a role like below.

USERPRINCIPALNAME() = [User]

Best Regards,

[Bian]

Thank you fpr your reply.

Yes I use relationship between BU and SystemUser.

I also use USERPRINCIPALNAME() in the RLS.

I can create two different Security Roles that works for Page 1 Or Page 2. But nothing that works for both at the same time.

I will continiue to use page filters instead of security filters but would be really interested to know if there is a better solution.

[v-jiascu-msft]

Hi @Bian ,

It seems you did it in the right way. Maybe your rules aren't applied to any user.

Can you test it in the Desktop by "view as role"?

Best Regards,

[Bian]

Yes I used view as Role.

The Problem is that the two pages needs to filter the Case table in different ways.

Page 1 needs to show 800 rows 

Page 2 need to show 850 rows

If I create a RLS that supports page 1. Page 2 will be missing 50 rows since it's filtered out with the security filter.

[v-jiascu-msft]

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 · Accepted solution]

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 · Accepted solution]

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