Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Resident Rockstar
Resident Rockstar

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors