Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Solved! Go to 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,
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.
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
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,
@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,
Both tables are imported.
The latest expression doesn't filter anything. No error. Just all entries as if looking at it without RSL applied.
Here is the current relationship status.
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,
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.
@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?
Best regards,
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
76 | |
74 | |
55 | |
45 |
User | Count |
---|---|
117 | |
106 | |
77 | |
66 | |
65 |