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.

  • Pingback: BI-NSIGHT – Power BI (Data Insights Summit 2017, Integrate Reports into SharePoint, Dual KPI, Featured Data Stories, Apply Filters to your report per User, Grid Custom Visual) – Gilbert Quevauvilliers – BI blog()

  • Koen Verbeeck

    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.

      • Koen Verbeeck

        Ah ok now I get it 🙂
        So you use RLS to “filter” the slicers, but not the actual data. Nice trick.

        • Kasper

          yeah I thought so too, it’s a sneaky one 🙂