Blog » Via Cathy Dumas
Fair warning: this is going to be complicated. In this post, I am going to assume that you’ve read the row security whitepaper, you are familiar with how parent/child hierarchies work in the tabular model, and you can deal with fairly complicated DAX concepts. For a primer on hierarchies, see Kasper’s post. For DAX, I recommend a few months of experience after learning DAX. If you don’t have that much DAX experience, let your eyes gently glaze over while I try to explain the DAX formulas, blindly copy/paste the formulas into your model, and hope for the best without trying to achieve true understanding (the latter is generally how I deal with DAX). Now to the post.
Hierarchies don’t work very well when row security is applied to a model. I went into details on this problem in the tabular security white paper, see the section “Example: Securing a model using parent/child hierarchies” for a detailed writeup.
I have a simple problem: in an org chart that can be up to 6 levels deep, count the number of reports in a user’s organization (both direct and indirect). The catch is that this model has been secured so that users can see employee data only for themselves and for their reports; not for their peers or superiors.
Defining a parent/child hierarchy as Kasper explains and then creating a measure using COUNT([User Name]) does not work properly here. If I drop the hierarchy onto a pivot table when connected to the model as a worker bee (individual contributor in Microsoft parlance), then I see the Count of Employees as 1 for every employee in the organization, including the bosses, because all of the other rows in the Employees table have been filtered out and cannot be counted. This is a side effect of the fact that visual totals always apply. Unlike in multidimensional models, these visual totals cannot be turned off. This is why the white paper from Microsoft recommends that you do not use hierarchies in pivot tables – DAX does not natively handle hierarchies as a first class citizen.
However, it is possible to work around this limitation in DAX. Greg Galloway helped me to devise this solution.
First step – open the Organizational Security example from http://securitytabularbi.codeplex.com/ and apply row security as described in the white paper.
Next – add some calculated columns. We will insert one calculated column per level of the hierarchy. This calc column with count all of the employees at that level of the hierarchy. Calculated columns work even when row security is applied, because the values in the calc columns are evaluated when the model is processed, which occurs before security applies. Therefore, the calc columns are static and the values do not change even when dynamic security is applied at runtime.
Create a calc column called L1HeadCount. Set the formula to this:
=COUNTROWS( FILTER( ALL(Employees), PATHCONTAINS([OrgHierarchyPath], EARLIER([Level1])) ) )
So what does this do?
Recall that calc columns are evaluated in a row context – that is, a row in the table is selected, and the value is computed for that particular row. Let’s take an example row:
This formula uses COUNTROWS() to count the number of rows in a custom table constructed using the FILTER() function. This custom table contains all rows in the Employees table (this is what the ALL() function does) that match a certain criteria. The criteria is specified in the PATHCONTAINS() function and the PATHCONTAINS() function is used to check the employee hierarchy for every single row in the Employee table for a particular manager. The second parameter to the PATHCONTAINS() function, EARLIER([Level1]), has the value for the Level1 manager (aka the big boss) for the selected user. So when ascott0 is the selected row, domain\andreas0 is the Level1 manager. This value, domain\andreas0, is fixed – so even though PATHCONTAINS() is checked 87 times, the parameter stays the same. The FILTER function for the row ascott0 returns a table where all the employees have the manager domain\andreas0 in the management chain. Counting the rows in this table yields 86 rows, which is the correct headcount for that manager.
Go ahead and create 5 more calc columns along these lines – L2HeadCount through L6HeadCount. Each time, simply change the parameter passed to the EARLIER() function, using [Level2] through [Level6] as appropriate. These calculated columns work systematically through the org chart to find the number of reports, finally terminating at 1 for the leaf node.
So we have the calculated columns now, but we can’t use those in a pivot table. We need to create a measure for that to work. Hold on to your hats, here’s the measure you need:
Head Count:= if( ISFILTERED([Level6]), MAX([L6HeadCount]), if( ISFILTERED([Level5]), MAX([L5HeadCount]), If( ISFILTERED([Level4]), MAX([L4HeadCount]), if( ISFILTERED([Level3]), Max([L3HeadCount]), if( ISFILTERED([Level2]), MAX([L2HeadCount]), Max( [L1HeadCount]) ) ) ) ) )
The ISFILTERED() function checks to see if that level of the hierarchy is selected and, if so, returns the appropriate calculated column value. I am not going to try to explain this in detail. Alberto Ferrari wrote a post about clever hierarchy handling in DAX, this is pretty much the same methodology.
Now you have a measure that you can use when you drop the Employee hierarchy into a pivot table with row security applied. Because all of the calc columns were pre-calculated before row security was applied, the correct number of rows for each manager are returned. To verify, Analyze in Excel connecting as domain\ascott0, and you will see that this is true.
In part 2 of this post I will show you how to blank out those numbers instead of displaying them. It is not so important (or perhaps undesirable) to blank them out for headcount, but perhaps if you had salary information you would want to blank them out.
If you would like this experience for using hierarchies changed, please feel free to vote up the Connect item I filed on this – http://connect.microsoft.com/SQLServer/feedback/details/733766/measure-values-in-hierarchies-do-not-roll-up-effectively-when-row-security-is-applied-to-a-tabular-model