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 .

10 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

  4. Hi Kasper
    I have tried to implement DRLS in the PBI Desktop File for sample ADW files but have hit a roadblock similar to at work where we currently do not have PBI Service, hence distribute PBI Desktop files to users via email or shared paths. The requirement is for the DRLS to take effect automatically when the user opens the PBI Desktop files from their local machines.
    In PBI Desktop file the problem is that every time any user opens the file, it opens in the default “None” role mode which means all data is visible to all users. This severely compromises data security.
    Only after the selecting the view as roles – “User”, does the DLRS come into effect. Why does the PBI file not open with a User role for any user. How can this be overcome and can the “User” be set as the default role or delete the “None” role.
    Please note we do not have PBI Service and this is similar to SSAS OLAP and TAB security using excel cubes which we implemented successfully.
    Thanks
    Uday

    1. Hi Uday, that will never work. Power BI desktop will not respect security, the user opening it is the admin and can see all the roles. You can only use this with a server component (PBI desktop is free).

      Kasper

      1. Hi Kasper,
        I have a scenario where I have implemented location wise dynamic rls security so when the user logins it fetches them particular country he is part of it.Dax -Country[CountryKey] = LOOKUPVALUE(
        ‘Security'[CountryKey],
        ‘Security'[EmailAddress],USERPRINCIPALNAME(),
        ‘Security'[CountryKey],Country[CountryKey]
        )
        Now client says I need to block “FRANCE&AUSTRALIA” do we need to add any “all except” function in above DAX or is there any other way to switch on/off access for this scenario.
        Note-im working on Direct query

        Thanks
        nag

        1. you mean you want to block both countries? In that case you should update the ‘Security'[CountryKey] column to contain both. AlleExcept does something completely different.

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.