Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Dynamic charting issue
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2014
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Dynamic charting issue

    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









    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?

  2. #2
    New Member
    Join Date
    Sep 2014
    Location
    Amersham, UK
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic charting issue

    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

  3. #3
    Board Regular
    Join Date
    Jun 2014
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic charting issue

    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",[Table].[Classification].[Categories],
    IIF([Measures].[CategorySelectionForecast]="Groups",[Table].[Classification].[Groups],
    IIF([Measures].[CategorySelectionForecast]="Subgroups",[Table].[Classification].[Subgroups],
    [Table].[Classification].[Brands]))))

    Set with error:

    IIF([Measures].[CategorySelectionForecast]="Categories",[Table].[Categories].[Categories],
    IIF([Measures].[CategorySelectionForecast]="Groups",[Table].[Groups].[Groups],
    IIF([Measures].[CategorySelectionForecast]="Subgroups",[Table].[Subgroups].[Subgroups],
    [Table].[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.

  4. #4
    New Member
    Join Date
    Sep 2014
    Location
    Amersham, UK
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic charting issue

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

  5. #5
    Board Regular
    Join Date
    Jun 2014
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic charting issue

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

    Dynamic rows which switching by disconnected slicer.

  6. #6
    New Member
    Join Date
    Sep 2014
    Location
    Amersham, UK
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic charting issue

    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.

  7. #7
    Board Regular
    Join Date
    Jun 2014
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic charting issue

    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.

  8. #8
    New Member
    Join Date
    Feb 2015
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic charting issue

    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....

  9. #9
    Board Regular
    Join Date
    Feb 2015
    Location
    Berlin
    Posts
    544
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic charting issue

    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

  10. #10
    Board Regular
    Join Date
    Jun 2014
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic charting issue

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •