Power BI Desktop Dynamic security cheat sheet

I recently created this simple Power BI desktop file that allows you to try out dynamic security with the new security relationship feature as described in this blog post.

The model contains the following tables:

image

  • Sales – contains the metrics, the fact table
  • Group – is the dimension table that groups a set of users. This can be anything in the real world, a department, a geography, productgroup, etc
  • User – the users with their username connecting to the reports
  • UserGroup – This ties individual users to the “group”, this usually is a M:M relationship where many users belong to multiple groups.

All relationships are set to single directional filtering except the one between Group and UserGroup. This allows the filter from the User table to filter the groups down to only those groups where the current user has access to, in turn filtering out the rows in the sales table for just those groups. I also enabled the Apply security filter in both directions property:

This property only shows up when you have the preview feature called “Enable cross filtering in both directions for DirectQuery” enabled that you can find in the options menu.

The report contains this a simple tablix that shows the products, sales and groups including the username and the userprinciplename (using the corresponding DAX functions) that is connecting to the model:

image

As you can see both return slightly different values, the userprinciplename function return the User-Principal-Name attribute from AD which is a UPN that is an Internet-style login name for a user based on the Internet standard RFC 822. Username returns the SAMAccountname which  is a logon name used to support clients and servers from previous version of Windows.

 

If you open the role management, there is one role in there that filters the username table using the earlier mentioned DAX USERNAME() function:

image

This allows you to test this inside of Power BI desktop or you can replace the username() function with any hardcoded string as well to try out how the security works. Here is a subset of the data as being secured by the role:

Capture

Hope this helps you get start started faster with dynamic RLS. You can download the file here: https://www.kasperonbi.com/uploads/security.zip .

6 Replies to “Power BI Desktop Dynamic security cheat sheet

  1. Thanks for this great blog post, as I was trying to figure this out myself. I would possibly suggest one key piece missing, which is included in the Bi-Directional relationship to “Apply security Filter in both directions” needs to be enabled!

    1. Hi,

      Ive applied the Bi-Directional relationship to “Apply security Filter in both directions” needs to be enabled! however my grand total value isnt corrent. Any ideas?

  2. Thanks for blog , we have slightly different scenario , fact table contians salesman username and he should see only his data and his manager should see his and all his salesman data. how to achieve this hirarchical RLS

  3. Kasper, imagine I am an ISV and I want to group the data from all my clients in a unique Data Warehouse, and then I apply RLS to distribute the content to the client. The tables from the data source have the same structure for all clients.
    My first idea was to include a column identifying the client in all tables, and apply the RLS for all of them in a static way.
    I cannot apply the RLS only on the Fact Tables because the values on the Dimensions are different for the clients, and they would be visible on Filters, for example.
    Is this really the only solution for my problem? It is quite complicated doing the way I am doing because I have over 30 tables in the model and over 40 clients.
    I wish there was a way of doing this dinamically.
    Thanks in advance

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.