rls without publish
1 Topic
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)Solved1.1KViews0likes3Comments