Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I am trying to give RLS a test but not getting anywhere when end users access reports & dashboards. Here's what I have set up, and done.
Data:
Data is coming from SQL Server through Reporting Services on-prem. The dataset contains sales data for districts in Western Australia, and contains two queries, Corporate Sales & Calendar.
I have added a new column UserID in Corporate Sales query in Query Editor and assign pbitest001 & pbitest002 based on random criteria. so now the rows in this dataset belongs to two users, pbitest001 & pbitest002. I created a report, and in 2nd tab, i dropped a matrix, plotted user id and district fields on columns, and revenue figures on columns. basically this matrix will tell me what user i am able to see after applying RLS in the cloud. I published the report in cloud, and dont' set up auto refresh, as yet.
PBI Service:
After publishing the report, I went to the dataset properties > security > RLS. Created a new rule in Corporate Sales which says [UserID] = "pbitest002", saved, and tested the role. It just shows the data for pbitest002 user. Sweet.
End User (PBITEST002)
When I log on to cloud using PBITEST002, i can still see data for PBITEST001. The column DaxUserName is a DAX measure, set to return value of Username(), which in this case returns current user name.
Not sure why 002 user is able to see data for 001? I tried fully qualified column name in the DAX expression set in rules but does not work. What's more confusing is that the test works as expected.
any ideas folks?
Thanks
Kaz
Solved! Go to Solution.
Hi Adam, and everyone,
I finally got it t work. the reason why RLS was not working as expected, was that the role member (PBITEST002) was configured as an admin of the workspace. RLS started working as expected once I set pbitest002 as member.
Thanks
Kaz
@Anonymous, did you check the same Power BI report when you do "Test data as role" and sign in as PBITEST002? The images you posted show that you're checking Power BI report when you do "Test data as role", however, you're checking with Power BI Dashboard when sign in as PBITEST002.
@Vicky_Song, yep, I checked the same report at the time of testing the role as well as after logging in as PBITEST002.
bump...
I just did a test on my end using the same fields as in your screenshot. I didn't encounter any issues. It worked as expected. I tested with both of the following DAX expressions for the rule.
[UserID]=username()
[UserID]="janedoe@guyinacube.com"
These both filtered data as expected.
It may be worth opening a support ticket to see what is happening on your end, as it isn't obvious.
Hi Adam,
Where did the filters work for you? Did they work at the time of testing, within the dataset security where you have the feature to 'test as role'? or did it work for you in an actual report? I have added screenshots (below) where I expect to see pbitest002 user to see only Central District, but the user can see all districts.
How can I log a support ticket? I have just tested out RLS after June 2016 update, and it has not changed a bit for me.
Thanks
Kaz
Hi Adam, and everyone,
I finally got it t work. the reason why RLS was not working as expected, was that the role member (PBITEST002) was configured as an admin of the workspace. RLS started working as expected once I set pbitest002 as member.
Thanks
Kaz
Um, looks like this feature is not there yet...
@Anonymous When you created role in RLS did you added your user PBITEST002 under Members tab ? Follow steps listed in this post.
@Anonymous Try this,
in your DAX you don't need to mention table name. so your dax should be [Column]="PBITEST002"
@ankitpatira Thanks Ankit. I tried using just the column name, and still does not work.. PBITest002 is still able to see rows for PBITETST001, though it works well at the time of testing RLS.
Thanks
Kaz
@Anonymous we have to troubleshoot this further. In Test Data as Role, you can also enter individual email address and preview for that, so enter PBITEST002 email address and check what it shows you in preview.
@ankitpatira already did that, and it works at the time of testing it, as it is illustrated in the RLS test image in my original post.
Thanks
Kaz
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.