title: Filtering data in the data modeler
body: <p>I have a sheet with many pivot tables connecting to the data modeller. The source data is based off a range in another worksheet. The reason I am using the data modeller is because I am trying to utilize it's Distinct Count feature. </p>
  
  <p>What I am trying to do is to filter all of my pivot tables in one swoop. I tried this through VBA (see code below), but I can't as too many lines are produced and the code bugs out. The idea is to filter out any data with a time to cancellation under 30.</p>
  
  <pre><code>Dim pt As PivotTable
  Dim ws As Worksheet
  
      For Each ws In ActiveWorkbook.Worksheets
          ws.Activate
          For Each pt In ActiveSheet.PivotTables
              pt.PivotFields("[Range1].[Time Till Cancellation]_
              .[Time Till Cancellation]").VisibleItemsList _
              = Array("[Range1].[Time Till Cancellation].&amp;[]", _
      "[Range1].[Time Till Cancellation].&amp;[31]", _
      "[Range1].[Time Till Cancellation].&amp;[32]", _
      "[Range1].[Time Till Cancellation].&amp;[33]", _
      "[Range1].[Time Till Cancellation].&amp;[34]", _
      "[Range1].[Time Till Cancellation].&amp;[35]", _
      "[Range1].[Time Till Cancellation].&amp;[36]", _
      "[Range1].[Time Till Cancellation].&amp;[37]", _
      "[Range1].[Time Till Cancellation].&amp;[38]", _
      "[Range1].[Time Till Cancellation].&amp;[39]", _
      "[Range1].[Time Till Cancellation].&amp;[40]", _
      "[Range1].[Time Till Cancellation].&amp;[41]", _
      "[Range1].[Time Till Cancellation].&amp;[42]", _
      "[Range1].[Time Till Cancellation].&amp;[43]", _
      "[Range1].[Time Till Cancellation].&amp;[44]", _
      "[Range1].[Time Till Cancellation].&amp;[45]", _
      "[Range1].[Time Till Cancellation].&amp;[46]", _
      "[Range1].[Time Till Cancellation].&amp;[47]", _
      "[Range1].[Time Till Cancellation].&amp;[48]", _
      "[Range1].[Time Till Cancellation].&amp;[49]", _
      "[Range1].[Time Till Cancellation].&amp;[50]", _
      "[Range1].[Time Till Cancellation].&amp;[51]", _ (...and so on up to 150)
  </code></pre>
  
  <p>My question is - </p>
  
  <p>Can I do this on through a feature within the data modeller ie some type of SELECT WHERE statement; or</p>
  
  <p>Is there an easier way to write this in VBA so I do not have too many lines of code (and yes, I could concatenate these lines into one single line but I am just wondering if there was an cleaner way)</p>
  
  <p>Many thanks,</p>
  
  <p>Chris</p>