Filtering a hierarchy

[rpmitchell]

Hello,

I have created an organizational hierarchy using the DAX patterns available from SQL BI. I used the parent-child hierarchy pattern, which is working great. I have 14,000 employees with 9 roll up levels.

The problem is I have no idea how to filter the hierarchy. For example, I want to see only VPs, and everyone that rolls up to them. Right now, I'm just dropping the "Hierarchy" field onto the row area, and it works great, but how do I filter it? Any ideas would be appreciated.

 

[Matt Allington]

Well dropping it onto Rows is a form of filtering. Another approach is you could add the Hierarchy into the filter section of the pivot. You can then use the drop down in the filter in the pivot and it will still behave as a hierarchy. Another approach is to add the column that contains the text VPs as a slicer. ie add the column, not the hierarchy as a slicer. I assume this will have the effect of pre-filtering the hierarchy in your rows

 

[rpmitchell]

Well, when I add a VP slicer, it filters out all the employees under the VP. So, it only shows the hierarchy from the top level down to the VP, but no one under that. I think the problem is that a VP can be on any level. For example, we have senior VPs, and executive VPs, so a simple VP could be on a Level 2, 3, or 4. Level 1 is the CEO, so that's the highest, with Level 9 being the lowest.

I've also tried creating a separate VP table and using that as a Filter, but it also eliminates anyone underneath. I just want to filter out anyone above a VP.

 

[rpmitchell]

Also, yes, I can select a particular VP from the drop down filter, and that works fine, but I want the pivot to magically give me all VPs and below, without manually selecting them.

 

[Matt Allington]

How about create another column in your employee table that identifies the job roles you want to see (Ie any VP and lower) and add that as a slicer