Dynamic charting issue

[Mer333]

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:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Brands
[/TD]
[TD]Categories
[/TD]
[TD]Subcategories
[/TD]
[/TR]
[TR]
[TD]Apple
[/TD]
[TD]Notebooks
[/TD]
[TD]1 edition
[/TD]
[/TR]
[TR]
[TD]Microsoft
[/TD]
[TD]Notebooks
[/TD]
[TD]2 edition
[/TD]
[/TR]
[TR]
[TD]Apple
[/TD]
[TD]Software
[/TD]
[TD]1 edition
[/TD]
[/TR]
[TR]
[TD]Microsoft
[/TD]
[TD]Software
[/TD]
[TD]2 edition
[/TD]
[/TR]
</tbody>[/TABLE]









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]

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]

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]

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]

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

Dynamic rows which switching by disconnected slicer.

 

[ChrisWebb]

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]

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]

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]

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]

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.