In an earlier blog, I described a solution for the use of Value Level Security (VLS) when users can be a member of multiple roles. (The general concept of VLS is described in our book Extreme DAX.) The problem with multiple role membership is that users may have access to both the positive rows (containing private data) and the negative rows (hiding private data) in the private table. Note that this is not a security problem: the user does not have access to data that she wouldn’t be allowed to see; instead, we get a duplication of labels and results in the Power BI report.

The solution in my earlier post solves this problem whenever the visual shows results on the level of individual rows in the (private) table by adding a visual-level filter. However, when the visual contains aggregated results, which happens when we only use a private attribute as a label in the visual, a filter will not work. The example below shows a table, based on the same example of the previous post, with salary costs by the private attribute ‘pay level’:

At first sight, nothing is wrong in this table: we have some salary costs for (employees with specific) pay levels, and we have salary costs for unspecified pay levels. The problem here is that the amount for the blank pay level row is not correct! To clarify this, I have added the employee name to the table and filtered it on Ambrocio Baker:

As you can see, Ambrocio has a bit over 54k of salary costs, and this amount is reported both under the blank pay level and under pay level 29.

With a visual-level filter, the only thing we can do is to hide the blank pay level row as a whole. This is obviously not what we want. The only solution is therefore to change the DAX code for the SalaryCosts measure to only return results for employees who we do not have access to private data for.

Fortunately, the DAX code for this measure is not that hard, as we already have a [ShowPrivate] measure that we used for the visual-level filter. What we need to do, is evaluate this measure on a per-employee basis:

Salary Costs (secured) =

SUMX(

    FILTER(DISTINCT(‘Employee (private)'[EmpNr]), [ShowPrivate] = 1),

    [SalaryCosts]

)

The result for Ambrocio is now:

The measure correctly does not return a result for Ambrocio and the blank pay level, as the result is already returned for pay level 29. The all-up table now looks like this:

Conclusion

The design goal for Value Level Security is to have a solution that is really secure, and with no to minimal changes to existing DAX measures. The conclusion we can draw from the general concept as described in Extreme DAX and these two blog posts, is:

• For all users who are a member of only one security role (and we should try to design our security roles to achieve this as much as possible), the approach as described in the book works well.
• For those users who are a member of multiple roles and who have access to both positive and negative rows, a visual-level filter can be added on each visual showing results on a detailed level to avoid having duplicate rows.
• When users who are in multiple roles need to work with visuals showing aggregate results, measures need to be changed according to the concept described in this post.