Using RLS for Dynamic filtering
Yes, you read the title correct. I know you might think “but.. RLS will remove the rows completely, not just filter it out”. Let me just outline the use case before I jump into the creative solution.
You have a report. Users look into this report and they’re allowed to see data that regards other users, but you want one page to reflect only the current users information. This could be a Sales representative that’s allowed to see all sales for the company in the report, but is mainly interested in follow up his or her budget. It’s not that the budgets are secret, it’s just convenient that you get your own data on this screen.
Perhaps you belong to a department and it would just be neat if one of the pages automatically was filtered to the same department that you belong to, but other pages of the report needs to show other departments as well.
There are ways we could solve this of course! We could build a separate set of display measures that will only show the values dynamically on USERPRINCIPLENAME(), but that’s a lot of work and doesn’t scale very well. We could import duplicate tables and use RLS on the ones for our custom page. Again not to scalable.
Allow me to propose the solution of “Filtering using RLS”! I first downloaded some sample data and made a simple report like this:
When my users open this page, they should only see their own data. On another page, they’re allowed to see the exact same view but for all users though.
In my model, I’ve related UserID to my totally not made up column “RandomUserID” in the facts table with data.
Let’s go ahead and make a copy of the dUser table but I only need the UPN column in there.
When this is created an loaded, Power BI Automatically creates a relationship between the tables like this. It’s of course a 1-1 cardinality.
The issue with this is that 1-1 will always go both ways and this includes security filtering, so if I set RLS filtering on this table now, it’s the same as if I just did it on the dUser table to begin with.
But what if I manually set the Cardinality to Many-to-one? Now I can still have it go both ways but I’m suddenly able to deselect “Apply security filter in both directions”. Just make sure that the many-side of the relationship is in the “RLS Filter”-table.
This means that I can now set up RLS on my dUser RLS Filter-table and it will not also filter the dUser-table!
As you can see here, being Ethan would only hide everyone else in the RLS table, not the actual dUser table.
But why would this be useful, you might say?
Because now we can set the UPN field as a filter on the current page! I’m setting it to “is not blank”.
Why does this work? Well because Ethan is only allowed to see 1 row in the RLS table, and that’s the row corresponding to his UPN. Only one user in the dUser will have a value in the RLS table related to it, all the others will be blank and due to our Page filter, these users now disappear.
This one single little filter on the page level makes it so that you can suddenly copy any other page in the report and instantly switch to it dynamically being filtered to the logged in user of the report.
OK so let’s make an alternative. Instead of simply saying your USERPRINCIPALNAME() is UPN, we can make a bit more advanced DAX filter for the RLS Query.
VAR SelectedVendorID =
CALCULATE(
SELECTEDVALUE(dUser[VendorID]),
dUser[UPN] = "ethan.muller@company.com",
ALL('dUser RLS FIlter')
)
VAR SelectedUsers =
SELECTCOLUMNS(
FILTER(
dUser,
dUser[VendorID] = SelectedVendorID
),
"UPN",dUser[UPN]
)
Return
'dUser RLS FIlter'[UPN] in SelectedUsersThis measure will pick up the “Vendor ID” value of the current user and then collect the UPN from all other users with the same Vendor ID, finally it filters the RLS table based on these users, meaning you automatically see your peers on the page and not just yourself.
At this point you’re probably thinking “OK I didn’t know I needed this before but now having one OR the other is simply not enough! I need them both in my life. NOW!”
Fear not, my friend! The RLS table is just a small dimensional table and you could, for example, just duplicate the RLS Filtering table and have them available at the same time.
Now I just add both filters in the same role like this:
In my report I can now have 3 pages with pre filters dynamically for the users! I could, for example, add a navigation on the page and make it look like you can pre filter to your own data on the same page.
About The Author
