Row Level Security based on country and department (plus View all role)

[IvanS]

Hi guys,

I am looking for RLS solution for sharing one report accross the branches - please find below business case.

1. I have MASTER dataset to which we have all reports connected - on this dataset there must be no RLS set up as some calculations are using cross-teams data.

2. There is new report (let's call it Global Report) which will be connected to MASTER dataset. This report will be used by different users within different branches and teams. For this report, I have imported table "Row Level Security"

3. There is table "Transactions" which have fields "Department" and "Country". Field "UniqueDepartmentRef" is unique key for "Country" & "Department" (example: Germany_Sales)

4. There is table "Row Level Security" which have fields "User name", "Email", "Department", "Country" and "View All ?". Again, there is field "UniqueDepartmentRef". There is Many to many relationship between these 2 tables.

Now, I would like to set up RLS as per below:

1. Users with flag "View All ?" = "x" will see all countries and all departments (no RLS filter)

2. Users with filled "Country" and blank "Department" will see all departments within selected country (e.g. Germany_Sales, Germany_Accounting, etc.)

3. Users with filled "Country" and filled "Department" will see only selected department within selected country (e.g. Germany_Sales only)

4. Otherwise (if user is not meeting above conditions or is missing in the list) will don't see any data

Based on tutorial from RADACAD (Dynamic Row Level Security with Manager Level Access in Power BI - RADACAD), I tried to set up RLS for points 1, 3 and 4 but it is giving me error. 

IF(
MAXX(
FILTER(
'Row level Security',
'Row level Security'[E-Mail]= USERNAME()
),

'Row level Security'[View All ?] = "x"),

'Transactions'[UniqueDepartmentRef]= LOOKUPVALUE('Row level Security'[UniqueDepartmentRef], 'Row level Security'[E-Mail], USERNAME()),
1=1

Another thing is that this report will be used by hundreds of employees across the globe and therefore I am trying to find solution that will not require manual addition of new users in "Security" tab in dataset but rather adjust the table "Row Level Security" by adding the user and define country and department in scope.

Thank you for any help

IvanS

[jdbuchanan71 · Accepted solution]

@IvanS 

First you want to remove the relationship betweene the 'Row Level Security' and the 'Transactions' tables
This DAX should give you the filtering you are looking for.

It would be entered in the Table Filter DAX expression for the role.

VAR UPNN =UserPrincipalNamee()
VAR _Country = Transactions[Country]
VAR _Dept = Transactions[UniqueDepartmentRef]
RETURN
    SWITCH (
        TRUE (),
       LOOKUPVALUEE (
            'Row level Security'[View All ?],
            'Row level Security'[E-mail], UPNN
        ) = "X", TRUE (),
        CALCULATE (
           COUNTROWSS ( 'Row level Security' ),
            'Row level Security'[E-mail] = UPNN,
            'Row level Security'[Country] = _Country,
            'Row level Security'[Department] = ""
        ) > 0, TRUE (),
        CALCULATE (
           COUNTROWSS ( 'Row level Security' ),
            'Row level Security'[E-mail] = UPNN,
            'Row level Security'[Country] = _Country,
            'Row level Security'[Department] = _Dept
        ) > 0, TRUE ()
    )

The easiest way to test your RLS code is to create a temporaryy calculated column on the table you are trying to filter then write the code in that column for testing to make sure it works the way you want.

I have attached my sample file for you to look at.

[RatanBhushan_05]

This is so perfect and easily fixed my problem.

[IvanS]

Thank you very much @jdbuchanan71 . I only had to use this function as measure (not in RLS) in my MASTER file as it would impact all reports.