none
How to create connection(s) between slicers, where data comes from different datasources in DataModel (2 PowerQueries) RRS feed

  • Question

  • Hello,

    I would like kindly ask you how to create connections between slicers if there are different data sources and the conditions are following:

    PivotTable1 - Slicer1 ("Slicer_Assigned_Support_Organization1")

    PivotTable2 - Slicer2 ("Slicer_Change_Assignee_Organization1" - different data source, but contains same data)

    For regular Pivot Tables where data gathered from excel sheets, I used following macro:

    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)   

    Dim sc1 As SlicerCache
    Dim sc2 As SlicerCache

    Dim SI1 As SlicerItem   

    Set sc1 = ThisWorkbook.SlicerCaches("Slicer_Assigned_Support_Organization1")
    Set sc2 = ThisWorkbook.SlicerCaches("Slicer_Change_Assignee_Organization1")

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    sc2.ClearManualFilter

        For Each SI1 In sc1.SlicerItems
                sc2.SlicerItems(SI1.Name).Selected = SI1.Selected
        Next SI1

    Application.EnableEvents = True
    Application.ScreenUpdating = True

    End Sub

    Unfortunately, when I moved from "sheet data model" to "OLAP data model" macro is not working at all.

    I have found something in the forum:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f1dbb115-c14c-421e-9561-07a79a9e6081/multiple-pivot-from-different-sources-slicers-controlled-by-a-single-slicer-works-with-regular

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f64322f0-715a-4497-9ff5-8797ad9ae261/how-to-connect-slicers-from-multiple-pivot-tables-and-different-data-sources-from-the-same?forum=exceldev

    but I wasn't able to apply it for my excel file.

    Could you please give me some recommendation for my macro.

    Thank you very much.

    Best Regards

    Richard













    Thursday, May 10, 2018 10:56 AM

All replies

  • Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    One slicer for two differently sourced PivotTables.
    With PQ trickery. No VBA macro.
    http://www.mediafire.com/file/ydqgyxdvw5s1lmq/05_10_18.xlsx
    http://www.mediafire.com/file/qjsdtqr46376seo/05_10_18.pdf

    Thursday, May 10, 2018 3:42 PM
  • Hello Richard Janov,

    What do you mean "OLAP data model"? I could use your code to control two pivot tables, but I failed to reproduce your issue since I did not know what about "OLAP data model".

    If you want to use once slicer to control different pivot tables, you could refer to Make a slicer available for use in another PivotTable to check if is helpful for you.

    Best Regards,

    Terry


    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.

    Monday, May 14, 2018 6:50 AM
  • Hi Terry,

    I mean by "OLAP Data Model" - Data are imported through Power Queries into different tables, edited in PowerQuery Editor and loaded to Data Model. After that are Pivot Tables created from this Data model. In this case, it's impossible to use the macro and report connections between different "sheets" in Data model.

    Thank you for your suggestions.

    Best Regards,

    Richard

    Monday, May 14, 2018 2:00 PM
  • Hello Richard Janov,

    Please try below code.

    Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    
    Dim sc1 As SlicerCache
    Dim sc2 As SlicerCache
    
    Dim SI1 As SlicerItem
    
    Set sc1 = ThisWorkbook.SlicerCaches("Slicer_Assigned_Support_Organization1")
    Set sc2 = ThisWorkbook.SlicerCaches("Slicer_Change_Assignee_Organization1")
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    
    sc1.VisibleSlicerItemsList = sc2.VisibleSlicerItemsList
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
    End Sub

    Best Regards,

    Terry


    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.

    Tuesday, May 15, 2018 6:27 AM
  • Hi Terry,

    When I had used this macro - nothing has happened. I'm uploading the file with few hundred rows to show you, what I mean.

    In Data model I've created relationship tab "Company Customers" which connects "Source folder" and "Source folder - CRQs", but this one is not used in slicers. Slicers in pivot tables are based on particular "Customer" field from each "Source folder" and "Source folder - CRQs".

    Thank you for your recommendations and suggestions. Here is the file:

    https://drive.google.com/open?id=1_ubg8lwqti4HTpWZmMXqIWXj4SPrVPhv

    Best Regards,

    Richard Janov




    Tuesday, May 15, 2018 1:23 PM
  • Hello Richard Janov,

    I'm afraid you could not do this work especially based on your current design.

    First at all, I think the sc1 and sc2 does not have same items. Their items are from different pivot tables, so in the code, their value may be [PivotTable 1].[Field A].[EIT_ACC II] and [PivotTable 2].[Field B].[EIT_ACC II]. We could not handle them as same item directly. 

    If just so, we could still take workaround for it. However, the key point is that the sc1 does not have a blank item like sc2. In this situation, while all the items are selected, we could not loop items in sc1 one by one so we could not set it to target slicer sc2.

    I'm not familiar with OLAP pivot table. Not sure how do you set sc1 and sc2. I think if you could set sc1 as sc2 with a blank item. Your requirement should be able to continue.

    Thanks for understanding,

    Best Regards,

    Terry


    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.

    Wednesday, May 16, 2018 9:26 AM