# Slicer to Control Axis Fields (Categories) or Legend Fields (Series)?



## joeshu26 (Feb 16, 2015)

Hello,

Does anyone know of a way to create a slicer to control Axis fields or Legend fields?  Possibly with a disconnected slicer?


----------



## ImkeF (Feb 17, 2015)

Have a look here: 5 Interactive Chart Techniques Come Together « PowerPivotPro


----------



## joeshu26 (Feb 17, 2015)

Thanks Imke, I was trying to reference that blog post for what I am trying to do but wasn't able to.  Basically I would like a slicer that changes the Axis Fields (Categories) section of the PowerPivot Field List.  

So I might have Region in the Axis Fields (Categories) section and my PivotTable or Chart will have data displayed by region.  I would Like to have a slicer that lets you choose Region, Sales Rep, or Country, etc.  and then the PivotTable or Chart will update to reflect the selection.

Thoughts?


----------



## ImkeF (Feb 17, 2015)

Is my underständig right?:
You want the slicer to trigger a *change *in the field, that sits in the row section (Pivot table) ie Axis Fields (Categories) for Pivot Charts. This is different of what slicers are normally used (only *filtering *on existing fields). 

What has been described in the above mentioned article is how to change the value-fields instead. So I can see quite some familiarites here (although the solution is more complex for his case).

Please confirm if my understanding of your need is correct - then we can move on


----------



## joeshu26 (Feb 17, 2015)

Yes, you have effectively described what I was attempting to describe.  

"You want the slicer to trigger a change in the field, that sits in the row section (Pivot table) ie Axis Fields (Categories) for Pivot Charts" is exactly what I'm looking for.

And I would like to apply the same logic to the change the field that sits in the Legends Fields (series) as well, if possible!


----------



## ImkeF (Feb 17, 2015)

Great. 
You seem to have some experience with cubeformulae - I make it short. Just say if you need any detailled explanation on any of it:

1) You're going to create a table as a source for your charts that is made of Cubeformulae.
2) The row and columns headings will be: CUBERANKEDMEMBER(..Cubeset, 1..n)
3) The Cubeset in there will dynamically be filled with the desired attributes by your slicer selections. Therefore you create a table (for each slicer) that contains all the names of the attributes that shall be displayed. You "harvest" the selction of the like you've learned in your previous post 
4) Then you pass that value on to the Cubeset, use ["&SlicerSelection&"]. This way your colums will show the different attributes that have been selected in your slicer.
5) Frame your Cuberankedmember-formulae in an IFERROR, to show blanks because your selection will most likely bring back different amounts of attributes (no of rows and columns)

Easiest way to achieve this is to create a Pivot from scratch, copy it and convert it to cubeformulae. There you have all the syntax that just needs to be replaced.

Here's some other usefull links on that Topic:
Pulling back the curtain: Intro to Cube Formulas « PowerPivotPro
Better Way to “Catch” Multiple Slicer Selections in a Formula «

hth, Imke


----------



## ImkeF (Feb 28, 2015)

Thought this was worth a blogpost (sorry, our site is in German, but the online translations aren't too bad, I think): Dynamische Charts in Excel | Blog | sqlXpert GmbH

There you can also find a file with an example which is quite simple and might speak for itself 
Imke


----------

