cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

DAX RLS filter for department value of sign in user

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!

1 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?

 

3.jpg


Best regards,

 

 

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

13 REPLIES 13
sturlaws
Super User
Super User

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.
Capture.PNG

 

Cheers,
Sturla

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

Anonymous
Not applicable

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?

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
Not applicable

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

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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
Not applicable

Here is the current relationship status.

BI-Relationship.png

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

 

4.jpg5.jpg6.jpg7.jpg

 


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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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
Not applicable

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

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?

 

3.jpg


Best regards,

 

 

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Dev Camp Session 30

Ted's Dev Camp - January 26, 2023

This session will provide guidance and teach campers the skills required to build Power BI reports that support multiple languages.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.