How to insert Slicers from PowerPivot hierarchies the right way
I think the Excel 2010 introduction of Slicers to filter your pivot tables makes it so much easier to filter the information in the pivot table and the ability to let slicers “Show items with no data last” was very helpful.
But when working on demo based on Adventure Works I was very surprised that it didn’t work when I added the Geography hierarchy as slicers to my Pivot table.
In this example I choose Canada as the Country Region and the state province slicer shows me the Canadian provinces but not as the first items … that’s still the Australian provinces 😦
So naturally I checked the setting of the State Province and it was apparently correct.
After trying a couple of times more using the “Insert Slicer” from the “Pivottable” tools table…
And thinking about MDX and how you must break hierarchies in MDX function DESCENDANTS to sort by using the BASC or BDESC – I thought it would be an idea to add the individual levels in the hierarchy as filters instead.
And voila – now the slicers “works”
So in order to make slicers from hierarchies as you would hope for – remember to select the level individually instead of from the hierarchy.