locked
How to connect slicers from multiple pivot tables different data source from the same PowerPivot RRS feed

  • Question

  • Hi All,

    Hope you are well. 

    I have several slicers from multiple pivot tables from different data sources with no relationship that I would like to connect,  all the tables are in the same PowerPivot.  I basically want to use one slicer instead of three or four of them since they all have the same items(values) to choose from. Can you help please? 

    Cheers, Zeinu


    • Edited by Zeinu Wednesday, January 11, 2017 1:21 PM
    Wednesday, January 11, 2017 12:59 PM

Answers

  • Hi Zeinu,

    If there is no same item in the tables or have mutiple value in the tables, we can't create a relationship between table. The relationship is basen on same item in table, the many-to-many relatiomship is not supported. And it is difficult to connect them

    After research, you'd better VBA. While this forum is used to resolve all the issues about Power Pivot for Excel. For for requirement using VBA, I personally suggest you post your theard to Excel developer forum, where you will get more professional support. Thanks for understanding. And I find a similar theard here, please review if it is helpful.

    Best Regards,
    Aneglia

     

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Michael Amadi Tuesday, April 18, 2017 5:48 AM
    • Marked as answer by Michael Amadi Saturday, June 3, 2017 11:20 AM
    Thursday, January 12, 2017 7:31 AM
  • Hi Zeinu,

    not sure if my understanding is correct, but to me it looks as if you can create dimension tables who contain unique values of your "slicers-to-be". Just connect them to your different existing "fact-tables".

    I would do this in Power Query:

    1) Reference one of your existing tables -> append all other tables in order to have all data at one place.

    2) Reference this big table and check the "slicer"-column -> rightclick mouse -> remove duplicates -> give your query a meaningful name and load to data model

    3) Repeat 2) for every desired slicer 

    4) Connect your new dimension tables to all existing data tables

    5) Hide the columns in your existing data tables that are connected to the new dimension tables from client view, in order to avoid stupid errors: You should only drag the fields from your dimension tables into your reports and/or use them as slicers from now on.


    Imke Feldmann TheBIccountant.com


    Sunday, January 15, 2017 7:27 AM
    Answerer

All replies

  • Hi Zeinu,

    Up to now, one slicer connections work from the PivotTable from same table. A single can control multiple pivot tables which must come from same source table. Once you set up your PivotTable using the different tables in the workbook you have to establish a relationship between the fields in the tables. After create relationship, right click the slicer->report connections, you will get the following screenshot, please show the pivot table wanted and click OK.

    If you have any issue, please feel free to ask.

    Best Regards,
    Angelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, January 12, 2017 2:04 AM
  • Hi Angelia, Thank you very much for your respons. The tables don't have a relationship, hence it is difficult to connect them in the way you are suggesting. I merely have the tables in one PowerPivot for convenience of have all the tables I need in one place. I think the answer could be resolved using VBA, and wondered if anyone has VBA scripts to connect slicers in PowerPivot. All the tables have the same two fields that I can use as slicers. Hence, the reason why I want what I choose to the first pivot table to the rest of the other pivot tables. As it is for the purpose of analysis I don't need to have relationships with the different tables unless I have to for the purpose of using one slicer to all. In which case how can I create a relationship with all the tables? Thank you very much once again Regards, Zeinu
    Thursday, January 12, 2017 3:11 AM
  • Hi Zeinu,

    If there is no same item in the tables or have mutiple value in the tables, we can't create a relationship between table. The relationship is basen on same item in table, the many-to-many relatiomship is not supported. And it is difficult to connect them

    After research, you'd better VBA. While this forum is used to resolve all the issues about Power Pivot for Excel. For for requirement using VBA, I personally suggest you post your theard to Excel developer forum, where you will get more professional support. Thanks for understanding. And I find a similar theard here, please review if it is helpful.

    Best Regards,
    Aneglia

     

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Michael Amadi Tuesday, April 18, 2017 5:48 AM
    • Marked as answer by Michael Amadi Saturday, June 3, 2017 11:20 AM
    Thursday, January 12, 2017 7:31 AM
  • Hi Aneglia,

    Thank you very much. I have looked the link you suggested, it is the same problem as mine but no obvious solution for it. I have posted my question to the developers, hopefully they have a VBA solution for it.

    Regards,

    Zeinu  

    Thursday, January 12, 2017 10:24 AM
  • Hi Zeinu,

    Got it. Thank you for understanding.

    Best Regards,
    Angelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 13, 2017 6:42 AM
  • Hi Zeinu,

    not sure if my understanding is correct, but to me it looks as if you can create dimension tables who contain unique values of your "slicers-to-be". Just connect them to your different existing "fact-tables".

    I would do this in Power Query:

    1) Reference one of your existing tables -> append all other tables in order to have all data at one place.

    2) Reference this big table and check the "slicer"-column -> rightclick mouse -> remove duplicates -> give your query a meaningful name and load to data model

    3) Repeat 2) for every desired slicer 

    4) Connect your new dimension tables to all existing data tables

    5) Hide the columns in your existing data tables that are connected to the new dimension tables from client view, in order to avoid stupid errors: You should only drag the fields from your dimension tables into your reports and/or use them as slicers from now on.


    Imke Feldmann TheBIccountant.com


    Sunday, January 15, 2017 7:27 AM
    Answerer