Hello Everyone,
I have used feedback from this community for a lot of work I have done in Power BI. I wanted to give back by showing a solution I came up with for a commonly requested item: dynamic RLS that works at the visual level, as opposed to working for an entire report, and which also doesn't require setting up security roles. This allows you to show summary visuals for an entire organization (e.g., a graph of total payroll expenses over time, by department), while filtering other visuals on the same page (e.g., a chart formated for export that has a list of all employees, with their salaries and other information), even if both use the same table.
How to:
First, you need a security list with all users listed by the email they will be using to log into the report. For embedded Power BI, this will be the credential passed through your website from a user's login, for BI Service, it is their login. Unfortunetly, I couldn't add a picture, but this is just a table with an email field and a budget center code field for my example.
Based on how you want your security set up, you may need something more complex than this. For our simple example, let's assume we only care about restricting access on the basis of budget centers. The budget director needs to see all budget centers. Their analyst team should only see certain centers. Thus the director has a row for every center, the analysts just have rows for the centers they work with.
This table will need to be pulled into your BI report. For our example, the table will be named "security." You will want to store it in a secure location online; anyone who can edit the table can bypass the security. BI reports using local files cannot be automatically refreshed, so that is not a good option. Sharepoint, Azure Blob, etc. all work, the file can be a simple CSV with these two columns. The email field should be the email used by every user for BI service or for your website if using embedded capacity. (Tip: using numeric codes for whatever you want security to filter on works faster than text codes).
Now we need a measure to filter our visual. For our example, let's pretend all our payroll data is in a table called "payroll_data." We need a measure that will give each row a value of 1 is a given user should be allowed to see it, and a zero/blank if they should not see it.
Here is our measure:
PAYROLL SECURITY FILTER =
VAR SECURITYLIST = CALCULATETABLE(VALUES(security[budget_center_code),FILTER(security,security[email] = USERPRINCIPALNAME()))
RETURN
CALCULATE(DISTINCTCOUNTNOBLANK('payroll_data'[budget_center_code]),FILTER('payroll_data','payroll_data'[budget_center_code] IN SECURITYLIST))
Let's break this down. Our variable is generating a list of all of our budget centers, which is what we want to use as the key for security. The filter is sorting that list to just those rows where the report users' emails exist. Next we are doing a count of a given column, the column used is not very important (more on that later). For our example, we're assuming our data is laid out so that each pay check is a single row, and lists the budget center code, which is what we want our security to filter on.
What is important is that, in a row level context, a count of this sort will always be equal to one, provided the data isn't filtered. Going row by row, a count measure for an individual row is always one, unless the row isn't counted. That last part is how our measure works. The filter at the end of our count calculation is ensuring that only the rows with budget center codes for which the user has a corresponding row in the security table get counted. If the budget center code is 108 and I don't have a row in the security table with my email next to code 108, then all rows with 108 in the budget center column in our payroll table would get a value of blank() for this measure. Note: If your security variable has blanks in your data table (payroll_data in this example), no one will be able to see them because they will be filtered due to there not being a corresponding email for blank values. I used DISTINCTCOUNTNOBLANK here to make that explicit in the logic for troubleshooting. If you have blanks and want people to see them, replace null values in M with a new numeric code and assign everyone's email to that code. If you only want certain users to see blanks, only give the new blank code to those users.
The last step is to apply this measure as a filter to the visual you want to apply security to. Drag it into the filter bar and set it so that the filter is set so that "PAYROLL SECURITY FILTER = 1". Your data will be filtered, but just for that visual. You're done!
"All my data just vanished; I have permissions for everything," will probably be your first thought. This is because BI Desktop does not pass your email credential through when using USERPRINCIPALNAME(). To see your filter at work, go to the "Modeling" tab in the ribbon and go to "View As," which is in the middle, under security. Select "other user" and type in your email, or another test email. The filter should now work.
You will also want to lock this filter, it's the little lock icon next to where you add filters (see here), since it defeats the purpose if people can just uncheck our filter. You will also want to make it so users can only "export data with current layout," (see here). Obviously this security method does not work if users have the ability to clear filters on the visual.
Other Considerations:
First, this single measure cannot be used for all visuals. If you have another table for employee health insurance data, and it has no relationship to your payroll table, then a measure based on a count of rows from the payroll table cannot be used to filter visuals only using data from the unrelated health insurance table. Luckily, the measure is easy to duplicate. You should create one for each table you intend to filter. Relationships are not a good way to use this methodology because counts can end up being greater than one for a given field in many to one/many to many relationships. You want your count to be completed at the row level. That said, tables using data from multiple tables can effectively be filtered this way, if you have a key (e.g. an employee ID) that is being displayed and is filtered out by your measure, since IDs that a user does not have access to will have a count value of zero and no field data associated with them will be displayed.
For a large organization using large datasets, this process could become slow and hit capacity. To speed it up, you can remove the requirement that BI calculate the security list on the fly. Simply add row level security roles and have the security table filtered so that [email] = USERPRINCIPALNAME(). Then you don't need the variable part of the measure, since your security values are already filtered, but you do need people assigned to roles.
Again, this method will not work if a user has permissions to edit visual level filters; it isn't airtight. Then they can just clear the security filter measure. I haven't had a huge issue with this because I think the filter bar looks ugly and include slicers instead. You can get around this limitation by incorporating your filter variable into measures of the data that you want to hide from a user, but that is more work. Obviously anyone with edit access who can download the pbix file of the report can also take the filter off. The use case here is for people who will only be reading reports, manipulating slicers, editing parameters, and exporting tables. This method won't secure backend access to the dataset (which is of course not the point, we would use regular RLS methods if we wanted to do that).
Other Uses:
This methodology can also be used to apply a filter to a reference table that is being used as a slicer for page level security. For example, if you have a slicer on your report that displays all an organization's departments, you can filter that slicer to only show departments a user has access to with a measure just like the one above. If the slicer is set to single select, you can add a row to your reference table to serve as a starting position that references no data in your tables (e.g., name it "No Department Selected"). When a user navigates to the page, it will show no data; when they go to select a department, they will only see departments they have access to as options. The advantage over traditional RLS set ups here is that you can create a filter on a slicer like this without having RLS filter the underlying table. This allows summary data from that table to still populate elsewhere in the report, while detailed information is hidden. This avoids work arounds that hit performance, such as duplicating the tables you want to show, and then having RLS apply to just one set of tables.
Again, this doesn't work if someone has the permissions to just delete the slicer or edit its filters, so it is limited in that respect.
