DAX RLS filter for department value of sign in user

[Anonymous]

Hello,

I have a BI report with 2 data sources. Active Directory and a SharePoint Online list. The 'Department' and 'Email' fields are shown from AD. These fields match the sharepoint list fields of 'Person.Department' and 'Person.Email'. There is a relationship between them.

I have a need to set a RLS to filter that takes the current logged in users department and filters on that. I can manually set the filter in the roles with [Department] = "Corrections". I need to be able to do this dynamically based on the user's department.

Any help would be greatly appreciated!

[sturlaws]

Hi @Anonymous 

have you looked at the USERNAME()/USERPRINCIPALNAME-functions?

you can use this in the filter of your email-table. If there is a relationship between email and department, the filter will propagate.

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[Anonymous]

When I use

NameofADSource[email]=UserPrincipalName()

as the role in the DAX expression, it returns just the row of that particular logged in user. I'm assuming there has to be a way to pass the department value of the logged in user to use as a filter for RLS?

[sturlaws]

It depends on your model.

You wrote that there was a relationship between your email-table and you department-table, do you have this relationship in your model? I also assume that you have table with some data in it that has a relationship to the deparment-table. If the direction of the filters are set properly, the filter on the user will filter the data table. 

There are other ways of creating dynamic RLS, but that will also depend on your model. Could you share a sample file or sample data?

Cheers,
Sturla

[Anonymous]

The relationships are like this:

ActiveDirectorySource                           SPO List Source               Cardinality             Cross Filter Direction

Department                                           Person.Department          Many to Many        Both

Email                                                      Person.Email                     Many to Many        Both          This relationship is active

[v-lid-msft]

Hi @Anonymous ,

We can add a RLS rule in SPO List Source Table to meet your requirement:

'SPO List Source'[Person.Email] = USERPRINCIPALNAME ()
    || 'SPO List Source'[Person.Department]
        IN SELECTCOLUMNS (
            FILTER (
                'ActiveDirectorySource',
                'ActiveDirectorySource'[Email] = USERPRINCIPALNAME ()
            ),
            "Department", [Person.Department]
        )

Best regards,

[Anonymous]

@v-lid-msft , this is what I get when I attempt that as a DAX expression on the SPOList for a RSL role:

Error Message:
An unexpected error occurred (file 'xmvsquery.cpp', line 3180, function 'XMVSColumn::Bind').

OS Version:
Microsoft Windows NT 10.0.18363.0 (x64 en-US)

CLR Version:
4.7 or later [Release Number = 528040]

The visual on the report is a table with 2x slicers beside it.

[v-lid-msft]

Hi @Anonymous ,

Dose other visual also have this error? Does the two tables use the Import Mode? We tried to optimize the rules as following:

VAR email = USERPRINCIPALNAME () 

RETURN 'SPO List Source'[Person.Email] = email
    || 'SPO List Source'[Person.Department]
        IN CALCULATETABLE (
            DISTINCT ( 'ActiveDirectorySource'[Person.Department] ),
            'ActiveDirectorySource'[Email] = email
        )

Best regards,

[Anonymous]

Both tables are imported.

The latest expression doesn't filter anything. No error. Just all entries as if looking at it without RSL applied.

[Anonymous]

Here is the current relationship status.

[v-lid-msft]

Hi @Anonymous ,

Thank you for your additional information, We can add a rls rules in "AssessmentResults" table to meet your requirement:

VAR e =
    USERPRINCIPALNAME ()
VAR D =
    SELECTCOLUMNS (
        FILTER ( ALL ( 'ActiveDirectorySource' ), 'ActiveDirectorySource'[Email] = e ),
        "De", [Department]
    )
RETURN
    [Person.Department] IN D

If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that we have shared?

Best regards,

[Anonymous]

In your example 2@b.com user is in Department "A". The RLS filtered result should only show users that are in Department "A".

I see where yours did it. I applied that to mine and it is showing all users from all departments. One thing that it did do, however, was take the department slicer and only show the value of the user's department in that filter. The table that shows the results still shows all users and all departments.

Also one employee might take the assessment multiple times. So the employee email is only listed one time in the AD source but could be twice in the assessment source. Each employee is only a member of one department.

[Anonymous]

@v-lid-msft - anymore ideas I can try? The last one got a filter correct, but it was on the data slicer instead of the table.

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

Sorr for our late reply, Could you please try to apply this rls roles in the Assesment Table instead of the ActiveDirect Table, it should filter the users belong to the department of login user, dose this rls works in the Test as user of Power Bi Desktop?

Best regards,