Blog » Via Cathy Dumas
In part 1 of this article, I showed how to use a hierarchy in a pivot table when row security is applied to a tabular model. In that example, I showed you a measure that displays the head count for all employees in the organization, regardless of the security model.
In this post, I will show you how to blank out the totals for the bosses instead of displaying them. Doing this with headcount is a bit of a contrived example, as it shouldn’t be a secret how many people work in a company. A better scenario is something like salary information, but head count will have to do for now. Again, this is complicated. You have been warned.
As a pre-requisite to this calculation, you must first construct the calculated columns L1HeadCount through L6HeadCount as instructed in Part 1.
Next, we will construct 7 measures. The first 6 measures are to check to see which level in the hierarchy is currently showing in the pivot table. The seventh measure actually performs the head count calculation.
The first measure looks like this:
Is L1 Filtered:= If( HASONEVALUE(Employees[Level1]), Not(ISBLANK(Values(Employees[Level1]))) && ISBLANK( COUNTROWS( FILTER( all( Employees), [UserAliasWithDomain] = VALUES( Employees[Level1]) ) ) ), FALSE() )
This measure checks to see if the selected level in the hierarchy has been filtered out by row security. It returns TRUE if there is a single level selected in the hierarchy and it has been filtered out by row security, and FALSE otherwise. The COUNTROWS() FILTER() bit is the one that checks to see if the level in the hierarchy has been affected by row security. I can’t really give a more detailed explanation of why each part is necessary; as I wrote this measure at the end of March and I have forgotten the details. Needless to say it’s not self-evident.
You can now add five more filters, for Level2 through Level6, simply substituting the column name [Level1] for the corresponding columns for the other levels.
Now, you can do the final calculation for the filtered head count:
Filtered Head Count:= if( ISFILTERED([Level6]) && NOT([Is L6 Filtered]), MAX([L6HeadCount]), if( ISFILTERED([Level5]) && NOT([Is L5 Filtered]), MAX([L5HeadCount]), If( ISFILTERED([Level4]) && NOT([Is L4 Filtered]), MAX([L4HeadCount]), if( ISFILTERED( [Level3]) && NOT( [Is L3 Filtered]), Max( [L3HeadCount]), if( ISFILTERED( [Level2]) && NOT( [Is L2 Filtered]), MAX( [L2HeadCount]), If( NOT( [Is L1 Filtered]), Max( [L1HeadCount]), BLANK( ) ) ) ) ) ) )
This measure says hey, if there is a single level of the hierarchy showing in the pivot table, and the level of the hierarchy has not been filtered out by row security, show the head count, otherwise, show a blank. Test this out in the same way you tested part 1, and you’ll see that it works as advertised.
Please, if you can optimize this DAX, post something better in the comments. Otherwise, copy/paste it into your model and just fiddle around with it blindly until something works.