Filter Pivot Row based on Multiple Measures

[DaveBlakeMAAT]

Hi,

I am hoping one of you fine people can assist, I am using VBA with a data model to draw Pivot Tables (the data is loaded to PowerQuery Only and saved as a connection). Drawing the Pivots was fine once i got my head around the nuances of CubeFields with the help of trusty WiseOwl videos!

The problem i have is how to filter a row based on multiple Measure criteria (The IF Statement in the code below), and how to then define if the row is visible (not sure if pi.Visible = TRUE would work).

Sub FilterCostCentresWhereNoSpend()

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
       
         Set pt = ActiveSheet.PivotTables("ptRunRateByCostCentre")
         Debug.Print pt
        
        Set pf = pt.PivotFields("[RunRateData].[Cost Centre Description].[Cost Centre Description]")
       
        For Each pi In pf.PivotItems
       
            If Measure1.value = 0 And Measure2.value = 0 Then
                Debug.Print pi
               
                pi.Visible = False
            Else
                pi.Visible = True
            End If
           
        Next pi
End Sub

Any Help is greatly appreciated!

Regards

Dave