Forum Discussion
RLS on Private servers
Hello.
My company is not using Microsoft Servers due to privacy issues, so we have our own servers to upload BI reports.
We have a several reports where a regular employee can see only personal results and managers can see only their group's results.
For this we use USERPRINCIPALNAME() measure to check if one is logged in with manager or regular_mployee and it calculated based on the columns, for example we have a source:
Sales | Regular_Employee | Manager |
100 | regular_employee1_mail | manager1_mail |
120 | regular_employee1_mail | manager1_mail |
140 | regular_employee2_mail | manager2_mail |
160 | regular_employee3_mail | manager2_mail |
and for this we created a "Staff list" table as another source:
Regular_Employee- mails | Manager - mails |
regular_employee1_mail |
|
regular_employee2_mail |
|
regular_employee3_mail |
|
| manager1_mail |
| manager2_mail |
and 2 measures
1) One calculates weather your USERPRINCIPALNAME() is in "Manager - mails" or "Regular_Employee- mails"
measure 1 =
if(CONTAINS('Staff list','Staff List'[Manager - mails],USERPRINCIPALNAME()),2,
if(CONTAINS('Staff List','Staff List'[Regular_Employee- mails],USERPRINCIPALNAME()),1,
0))
What it does is - If your USERNAMEPRINCIPAL is in manager column than you are 2, if it is in employeemail column, you are 1, if non then 0.
2) and the second measure,
measure 2 =
VAR Employee = calculate(sum(table[column]), USERPRINCIPALNAME() = table[column])
VAR Manager= calculate(sum(table[column]), USERPRINCIPALNAME() = table[column])
RETURN
IF([measure 1] = 2, Manager,
IF([measure 1] = 1, Employee,
BLANK()
).
So yeah, it does work, BUT for this to work we have to create individual measures for each visual and each column in visual .... BLAH!
Here starts my questions:
Is there any kind of a workaround way where we will not have to create each individual measures?
Can we just drag a column and let is somehow filter by personal results? (Without using Microsoft servers if that is how they work)
Nick_Kalichava wrote:
Could you provide an example or go into more details? I dont really follow you.
Dynamic Row Level Security with Power BI Made Simple - RADACAD
3 Replies
- d_gosbellSuper User
If your 'staff list' table has a relationship to the rest of your data model you can get rid of all those extra measures and just create a single role which has a filter like the following:
'Staff List'[Manager - mails] = USERPRINCIPALNAME() || 'Staff List'[Regular_Employee- mails] = USERPRINCIPALNAME()
Then you can create simple measures like SUM( table[column]) and the above filter will be applied by the Role.
- Nick_KalichavaFrequent Visitor
Could you provide an example or go into more details? I dont really follow you.
- d_gosbellSuper User
Nick_Kalichava wrote:
Could you provide an example or go into more details? I dont really follow you.
Dynamic Row Level Security with Power BI Made Simple - RADACAD