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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Bian
Helper II
Helper II

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.

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)

2 ACCEPTED SOLUTIONS

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

View solution in original post

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

 

 

View solution in original post

7 REPLIES 7
v-jiascu-msft
Employee
Employee

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,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

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,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

 

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,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.