This was an interesting case I did a while ago where they wanted to have a report that automatically provided a set of filters to the users, but not just any filters, no special filters tailored to them based on location and position in the organization. In this blog post we’ll look at how you can achieve this using RLS without actually securing the data.
Before we start let’s look at the end result, on the report below a user can select Customer set1 and Territory set1 and get the data filtered down to their set of customers and territories. Every user will see their own predefined filters. Not selecting anything will show all the data and allows for the normal exploration.
These filters can be combined if needed and will allow for filtering on all the visuals. You can use these sets anywhere from slicers to the filter pane.
Now doing the same thing for a different user will give different results:
The interesting thing here is that these sets are defined as part of the model data and can be managed by anything, a sharepoint list, a database or an Excel spreadsheet. It all depends on how fancy you want to get, it is just data.
Here is how it works, I extended an existing regular star schema with a few tables to allow filtering as shown above:
The CustomerFilter Table is connected with the DimCustomer table using a Bi Directional relationship, this makes sure that that any filters placed on the CustomerFilter table actually also flow down to the DimCustomer table. This is key for this scenario, this is only possible with SSAS 2016 and Power BI. As you might notice it is a small variation on the Dynamic Row Level security pattern. Another key difference here is that I have NOT checked the “Apply security filter in both directions checkbox” on the relationships between the DimCustomer and CustomerFilter table. This makes it possible to actually see all the sales data, the RLS security only filters the CustomerFilter filter but doesnt flow to anywhere downstream from that.
The CustomerFilter table is a very simple table that contains a few simple columns. The username for the user that this set is defined for, the customerKey contains the customers in this set and the CustomerFilters column that defines which Set this belongs to. The last column is what is shown in the UI:
This the table you need to fill to define the sets with whatever you want. You can have someone hard code it or you can create an automated process to do this.
The last table is a user’s table that only contains usernames, you can probably use a calculated table and get the values from the previous table filter table. Here I added a simple RLS expression, this will filter out the filters so the list will only show the custom filters as defined for the user connected to the model. Of course this step is optional and won’t do anything else then trim down the list of options for the user:
You can replace the hard-coded string with USERPRINCIPALNAME() to get the username automatically.
And that’s it.. hope this might give you some ideas, I understand this is not something you would use day to day but might get useful in some cases.
How exactly does this differ from normal RLS?
I you have a RLS security expression on the SalesUsers table, it will filter the SalesRegion Filter table (which is shown in the slicer). But, won’t this also filter the DimSalesTerritory table and finally the Internet Sales Fact table?
How do you show all data, when there is RLS in place?
It is not very different then normal RLS as mentioned in the blog post ” As you might notice it is a small variation on the Dynamic Row Level security pattern.” The reason it doesn’t filter out the fact table is because it is using a BIDI relationship and I havent checked the use security for BiDi checkbox. If I would it would be just like a regular relationship. Good question, i’ll update the blog post as well.
Ah ok now I get it 🙂
So you use RLS to “filter” the slicers, but not the actual data. Nice trick.
yeah I thought so too, it’s a sneaky one 🙂
Neat trick!
I have used a similar solution for a customer. In our solution the person should be able to see their own numbers and the total for the team (and other variations). For that case we implemented the dynamic filtering through the DAX measures.
For example:
Sales (personal):=
VAR UserName = USERPRINCIPALNAME()
RETURN
CALCULATE (SUM(FactTable[SalesAmount]);CustomerFilterTable[UserName]=UserName)
If you want to use the same measures for the team calculation (instead of having a new measure Sales (team)) you can implement this through a disconnected slicer and SWITCH formula.
This looks like it might work for something we’ve been banging our heads against. I can’t tell if the article is missing an image, but it seems like there is a gap where an image depicting an example of the CustomerFilter table data – where the CustomerKey and CustomerFilters columns are filled in. How did you populate that table to get the desired behavior?