Automatically apply filters to your report per user

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.

clip_image002

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.

image

Now doing the same thing for a different user will give different results:

image

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:

clip_image006

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:

image

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:

image

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.