# Dynamic charting issue



## Mer333 (Nov 17, 2014)

Hello everyone!

I have a problem considering that technique - Dynamic Charting In Power Pivot « PowerPivotPro

When I create a hierarchy in PP it contains duplicated names on the lower levels due to logic below:


*Brands*
*Categories*
*Subcategories*
Apple
Notebooks
1 edition
Microsoft
Notebooks
2 edition
Apple
Software
1 edition
Microsoft
Software
2 edition


<tbody>

</tbody>








So therefore it produces a problem after switching. For instance, when I switch a slicer to *Categories* it contains duplicated categories - in this example all four above, twice notebooks, twice software. But I expect to get only unique names and sums for them.

Any ideas how to solve it?


----------



## ChrisWebb (Nov 17, 2014)

The answer is not to create a slicer from the hierarchy you've created. Instead create a hierarchy from the Categories field, which will only display the distinct values in the Categories column. The same goes for the MDX you're using - if you get all the members from the Categories level of the hierarchy you've created you'll get duplicate names, but if you get distinct categories you need to get the members from the Categories hierarchy (which should only have Categories on it, no Brands or Subcategories) something like this: [MyTable].[Categories].[Categories].MEMBERS

Chris


----------



## Mer333 (Nov 17, 2014)

The problem is if I have a set with different hierarchies it return an error: "Cannot handle a set that changes dimensionality."

Original set:

IIF([Measures].[CategorySelectionForecast]="Categories",

.[Classification].[Categories], 
IIF([Measures].[CategorySelectionForecast]="Groups",


.[Classification].[Groups],
IIF([Measures].[CategorySelectionForecast]="Subgroups",


.[Classification].[Subgroups],


.[Classification].[Brands]))))

Set with error:

IIF([Measures].[CategorySelectionForecast]="Categories",


.[Categories].[Categories], 
IIF([Measures].[CategorySelectionForecast]="Groups",


.[Groups].[Groups],
IIF([Measures].[CategorySelectionForecast]="Subgroups",


.[Subgroups].[Subgroups],


.[Brands].[Brands]))))

With slicer itself I have no problems because it is disconnected slicer that looks like:
Brands
Categories
Groups
Subgroups

I meant that after I switch it to Categories I receive duplicated categories in a set.


----------



## ChrisWebb (Nov 17, 2014)

What type of calculation are you trying to do here? There may be another way of writing it that doesn't need an IIF().


----------



## Mer333 (Nov 17, 2014)

I do exactly as described here - Dynamic Charting In Power Pivot « PowerPivotPro

Dynamic rows which switching by disconnected slicer.


----------



## ChrisWebb (Nov 17, 2014)

Ah, ok. I see what the problem is now - and the bad news is that I don't think there's a solution if you use this particular technique. I've tried a few ideas (eg creating a set that contains tuples containing members from all hierarchies) but nothing works; I'll keep thinking about it though. You'll have to use a different method to switch what you're displaying in your PivotTable/PivotChart and not a named set.


----------



## Mer333 (Nov 17, 2014)

Unfortunately I don't know any other similar technique for dynamic rows controlled by slicer. If you know something it would be good. 
Hope you'll find a solution though.


----------



## newuser1234 (Feb 20, 2015)

Hello

I've spent 3 nights looking for a solution on the net without success..essentially what i want to do is to change the rows in pivot table using the slicer which i think is what Mer333 trying to do?

Anyone out there has a solution to his please? I've tried many things and losing sleep over this....


----------



## ImkeF (Mar 2, 2015)

Hi there,

@Mer333: You might try this approach: Dynamische Charts in Excel - lets you add any attribute to your Legend (or Axis) selection without showing duplicates, crossfilter-effect should do the hierarchy.

@newuser1234: If you just want change the rows/columns without further reducing them, this is an easier approach: Dynamische Charts in Excel | Blog | sqlXpert GmbH

Sorry, our blog is in German - let me know if you need any help with the automated translation. The files with the examples might help as well.

Let me know if it worked, Imke


----------



## Mer333 (Mar 3, 2015)

Hi Imke! 

WoW! I forefeel there is something that's definately game-changing.

It would be great if you translate all these articles into English.


----------



## Mer333 (Nov 17, 2014)

Hello everyone!

I have a problem considering that technique - Dynamic Charting In Power Pivot « PowerPivotPro

When I create a hierarchy in PP it contains duplicated names on the lower levels due to logic below:


*Brands*
*Categories*
*Subcategories*
Apple
Notebooks
1 edition
Microsoft
Notebooks
2 edition
Apple
Software
1 edition
Microsoft
Software
2 edition


<tbody>

</tbody>








So therefore it produces a problem after switching. For instance, when I switch a slicer to *Categories* it contains duplicated categories - in this example all four above, twice notebooks, twice software. But I expect to get only unique names and sums for them.

Any ideas how to solve it?


----------



## Mer333 (Mar 3, 2015)

The problem is that there are blanks in the chart's legend.


----------



## ImkeF (Mar 3, 2015)

Hi Mer333,
unfortunately I cannot see a way around a macro here:


```
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim cell As Range
    Application.ScreenUpdating = False
    For Each cell In Worksheets("HideLogic").Range("D13:O13")
        cell.EntireColumn.Hidden = cell.Value = 0
    Next cell
    Application.ScreenUpdating = True
End Sub
```

This will hide all empty columns everytime you change the slicer-selections (and thereby changing the connected pivottable) and then the blanks in the legends will disappear (you need to change the IFERROR-Value from "" to 0).

Will put that file in the blogpost as well.

It's really frustrating, that Excel charts have no way to dynamically adjust the number of colums (data series). It's possible with rows (Axis entries) (although quite laborious).
Maybe with the next realease? (Hope springs eternal )


----------



## newuser1234 (Mar 4, 2015)

Hi ImkeF

Thanks very much for taking the time to reply to my question.

The example helped me gained a lot of insight into cube functions! It is actually doing exactly what I want. However, instead of using cube I wanted to do it in Pivot table.

I want a pivot and with a slicer and the user can select which category (rows) they want to view by. Is that possible at all? thanks.

p.s im still quite fresh to powerpivot so still learning..


----------



## ImkeF (Mar 4, 2015)

The only way I can think of is macro, but this is not my area of expertise.
Good luck


----------



## scottsen (Mar 15, 2015)

You all inspired me. 

Dynamically changing chart axis | Power Pivot | Tiny Lizard


----------



## ImkeF (Mar 16, 2015)

Thanks Scott, 
this is a triple A: *A*bsolutely *A*wesome (*A*lthough not mine) 
Love it!
Imke


----------



## Mer333 (Mar 16, 2015)

scottsen said:


> You all inspired me.
> 
> Dynamically changing chart axis | Power Pivot | Tiny Lizard



OMG!!! So pretty clever though quite simple... I will definitely work with this technique and see how it works with real examples and what is the possible pitfalls.

This technique opens a bunch of possibilities to implement that approach not only in excel tables but also in Power View and Power Designer dashboards...


----------



## ImkeF (Mar 16, 2015)

Definitely - really exiting, isn't it?

But how about an even more remote usecase: Imagine a macro specialist, who had built some very nice interactive dashboards for standard excel and is now facing the challenge to publish them in Excel Services (Sharepoint, Office 365) where macros don’t run.

WDYT?: Wouldn’t this technique be worth the efford? I mean, if you are smart enough to program macros like this, at least in my eyes the new steps to learn for this technique seem doable.


----------

