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:
- 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
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:
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:
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:
Hope this helps you get start started faster with dynamic RLS. You can download the file here: http://www.kasperonbi.com/uploads/security.zip .