Forum Discussion

Nick_Kalichava's avatar
Nick_Kalichava
Frequent Visitor
3 years ago
Solved

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)

 

3 Replies

  • 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.