• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

One Slicer to control 2 Pivot Tables with different data source

D

Dokat

Member
Hi,

I have 2 pivot tables with different data sources and i am trying to control pvt tables with one slicer. I used power query to add the tables to data model. My issue is i cant connect 2 tables in power pivot as it doesnt let me create many to many relations. Did anyone come across a similar issue and solve for it.

Thank you
 
hi Khalid,

unfortunately those methods didn't work for me. My pvt tables are based on a two different data source and it doesn't let me connect tables because of many to many relationship.

thanks
 
Hi @Dokat

Two solutions could be useful for you:

1. Using PowerQuery, you can combine the two tables into one, put it as a connection an make a Pivot Table with this connection

link:

2. Using PowerPivot, you can create a new Table with the ID's of each table, and later, use CALCULATE Dax function to resolve

link:
http://tinylizard.com/many-to-many-relationships/

Later, you can make a Slicer to manage it. Blessings!
 
I tried the code however slicer selection doesn't return the correct values in the second pivot table. It makes multiple selections

For Example: If i select "Sun"in slicer it brings the correct value in main pivot table however it selects and returns values for "Sun","Ajax","Comet"... Do you know what may cause this?



Code: 
Private Sub Workbook_SheetPivotTableChangeSync(ByVal Sh As Object, ByVal Target As PivotTable)

    Const MainSlicer As String = "Slicer_Brand"
    Const Slicer1 As String = "Slicer_Brand1"

    If Target.Name = "PivotTable1" Then

        Application.ScreenUpdating = False


        Application.EnableEvents = False

        On Error GoTo Catch

        Dim slMainSlicer As Slicer
        Dim slMainSlicercache As SlicerCache
        Dim slItem As SlicerItem


        Set slMainSlicercache = ThisWorkbook.SlicerCaches(MainSlicer)

        ActiveWorkbook.SlicerCaches(Slicer1).ClearManualFilter
    

        For Each slItem In slMainSlicercache.SlicerItems

            ThisWorkbook.SlicerCaches(Slicer1).SlicerItems(slItem.Name).Selected = slItem.Selected
        
    
        Next

    End If

Catch:
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub
 
Last edited by a moderator:
Upload sample workbook where you have this issue.

I suspect your source data or slicer set up is causing this issue. But can't give you solution without knowing your set up.
 
I see your issue.

There are items present in 2nd slicer which are not present in main slicer. This is causing those item to appear.

You need to modify your code like below.

Note that it's going take longer to run it since it has to iterate through every item in 2nd slicer.

Code: 
Private Sub Workbook_SheetPivotTableChangeSync(ByVal Sh As Object, ByVal Target As PivotTable)

    Const MainSlicer As String = "Slicer_Brand"
    Const Slicer1 As String = "Slicer_Brand1"
    If Target.Name = "PivotTable1" Then

        Application.ScreenUpdating = False

        Application.EnableEvents = False

        On Error GoTo Catch

        Dim slMainSlicer As Slicer
        Dim slMainSlicercache As SlicerCache
        Dim slItem As SlicerItem

        Set slMainSlicercache = ThisWorkbook.SlicerCaches(MainSlicer)

        ActiveWorkbook.SlicerCaches(Slicer1).ClearManualFilter
       
        For Each slItem In ThisWorkbook.SlicerCaches(Slicer1).SlicerItems
            If slMainSlicercache.SlicerItems(slItem.Name).Selected Then
                slItem.Selected = True
            Else
                slItem.Selected = False
            End If
        Next

    End If

Catch:
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub
 
I'd strongly recommend using PowerQuery if you can (even if you can't merge tables or one reason or another).

You can load data to DataModel (OLAP) and leverage .VisibleSlicerItemsList property of SlicerCaches instead of iterating through each SlicerItem.

You can read some detail in thread below.
http://chandoo.org/forum/threads/slicer-selection-via-vba.32182/

Also, you may try following modification to code. It will force manual update mode until code finishes running and turn it back to auto update at end of code (should speed up things significantly).

Code: 
Private Sub Workbook_SheetPivotTableChangeSync(ByVal Sh As Object, ByVal Target As PivotTable)

    Const MainSlicer As String = "Slicer_Brand"
    Const Slicer1 As String = "Slicer_Brand1"
    Dim pvt As PivotTable
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        For Each pvt In ws.PivotTables
            pvt.ManualUpdate = True
        Next pvt
    Next ws
   
    If Target.Name = "PivotTable1" Then

        Application.ScreenUpdating = False

        Application.EnableEvents = False

        On Error GoTo Catch

        Dim slMainSlicer As Slicer
        Dim slMainSlicercache As SlicerCache
        Dim slItem As SlicerItem

        Set slMainSlicercache = ThisWorkbook.SlicerCaches(MainSlicer)

        ActiveWorkbook.SlicerCaches(Slicer1).ClearManualFilter
     
        For Each slItem In ThisWorkbook.SlicerCaches(Slicer1).SlicerItems
            If slMainSlicercache.SlicerItems(slItem.Name).Selected Then
                slItem.Selected = True
            Else
                slItem.Selected = False
            End If
        Next

    End If

Catch:
    Application.EnableEvents = True
    Application.ScreenUpdating = True

    For Each ws In ThisWorkbook.Worksheets
        For Each pvt In ws.PivotTables
            pvt.ManualUpdate = False
        Next pvt
    Next ws
End Sub
 
I tried Power Query however wasnt able to connect 2 data sources because of many to many relationship. thats why i am trying to see if there is a way around it. Thank you so much for your help
 
How can i add second or 3rd main slicer to the code? I will have multiple slicers controlling 2 pivot tables from 2 data sources. Thank you

This is the code i am using but getting error message

Code: 
Private Sub Workbook_SheetPivotTableChangeSync(ByVal Sh As Object, ByVal Target As PivotTable)

    Const MainSlicer As String = "Slicer_Brand"
    Const Slicer1 As String = "Slicer_Brand1"
  Const MainSlicer2 As String = "Slicer_Mfr"
    Const Slicer2 As String = "Slicer_Mfr1"
    Dim pvt As PivotTable
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        For Each pvt In ws.PivotTables
            pvt.ManualUpdate = True
        Next pvt
    Next ws
  
    If Target.Name = "PivotTable4" Then

        Application.ScreenUpdating = False

        Application.EnableEvents = False

        On Error GoTo Catch

        Dim slMainSlicer As Slicer
        Dim slMainSlicercache As SlicerCache
        Dim slItem As SlicerItem

        Set slMainSlicercache = ThisWorkbook.SlicerCaches(MainSlicer)

        ActiveWorkbook.SlicerCaches(Slicer1).ClearManualFilter
    
        For Each slItem In ThisWorkbook.SlicerCaches(Slicer1).SlicerItems
            If slMainSlicercache.SlicerItems(slItem.Name).Selected Then
                slItem.Selected = True
                Set slMainSlicercache = ThisWorkbook.SlicerCaches(MainSlicer)


        Dim slMainSlicer2 As Slicer
        Dim slMainSlicer2cache As SlicerCache
        Dim slItem2 As SlicerItem


        Set slMainSlicercache = ThisWorkbook.SlicerCaches(MainSlicer2)

        ActiveWorkbook.SlicerCaches(Slicer1).ClearManualFilter
    
    
        For Each slItem2 In ThisWorkbook.SlicerCaches(Slicer2).SlicerItems
            If slMainSlicercache.SlicerItems(slItem.Name).Selected Then
                slItem.Selected = True
            Else
                slItem.Selected = False
            End If
        Next

    End If

Catch:
    Application.EnableEvents = True
    Application.ScreenUpdating = True

    For Each ws In ThisWorkbook.Worksheets
        For Each pvt In ws.PivotTables
            pvt.ManualUpdate = False
        Next pvt
    Next ws
End Sub
 
Last edited:
You must log in or register to reply here.
Back
Top