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 .

  • Gilbert Quevauvilliers

    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!

    • Dave Kerby

      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?

  • Pingback: Row Level Security in Power BI - DataChant()

  • Surendra Thota

    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