5 Replies Latest reply on Feb 19, 2016 9:12 AM by Simon Runc

    Confusion about filter order of operations

    trevor.badorrek.0

      I'm having trouble with setting filters on my workbook and looking for a solution--or at least, a workaround.

       

      I'm trying to filter a dimension based on a Top 10 SUM of a measure. I've created this first filter, and everything works fine: my top 10 values are displayed.

       

      I then want to apply a couple more dimension filters. However, instead of keeping my top 10 values, my list becomes smaller than 10, as it's filtering out rows based on these additional dimension filters. INSTEAD, I want it to calculate a new top 10, just based on the rows that are included with the various dimension filters.

       

      I realize this is an order of operations issue with the way Tableau calculates filters. Is there a way I can set this up to behave the way I would like?

        • 1. Re: Confusion about filter order of operations
          Simon Runc

          hi Trevor,

           

          So one way to achieve this (and the one I tend to for due to its flexibility) is to use index(), and then filter the Top N using this index value.

           

          There is an explanation and workbook on this post

          Re: Top N in a category in a country??

           

          Not your exact situation, so if you can't get this to work for you, or doesn't make sense please post back

           

          ...I should have also directed you to this very interesting discussion on the 'Order of Operation'...

          Evolution of the Order of Operations Diagram

          as you can see when it comes to "Confusion about filter order of operations" you are not alone, including some of Tableau's Great & Good!!!

          1 of 1 people found this helpful
          • 2. Re: Confusion about filter order of operations
            trevor.badorrek.0

            Interesting. On first glance, that may work for me. Need to play around with it some more.

             

            I also realized a better example of what I'm trying to do is found in Alexander Mou's post here. I've tried changing my filters to context filters, but that didn't work. I'm actually trying to do this using filters across a primary source and secondary source, so not sure if that's what's causing the problem.

            • 3. Re: Confusion about filter order of operations
              trevor.badorrek.0

              Adding the INDEX filter appears to work for the most part with the way I've got this set up.

               

              The only problem I'm still finding is that if I change the criteria of my dimension filters in a way that adds more rows, the list is no longer sorted properly. So it requires me to undo my INDEX filter, resort the rows from top to bottom, then re-apply my INDEX filter.

              • 4. Re: Confusion about filter order of operations
                Simon Runc

                Yes Alexander has done a lot of great work in trying to decipher the filter pipeline...and yes adding filters to context is a way to 'force' them up the pipeline (although there is a performance cost to this, but you'll only notice if you are using a fairly large data-set).

                 

                Yes blending can be an issue...and again this is due to when the 'blend' takes place in the pipeline! (especially when applying secondary datasource filters). Have a go with the index() as by setting this up the right way, will always show you 10 of something...also being a Table Calc filter (which as you can see gets performed last) means that what actually happens it that the view is filtered but the underlying data isn't.

                 

                If you can't get it to work...I might need to see a mock up of your situation so I can have a bit of a play.

                • 5. Re: Confusion about filter order of operations
                  Simon Runc

                  So 2 things here. One is to ensure you set up the INDEX() partitioning and addressing to correct dimensional levels, and the second thing (if you look at the post I referred you to), is that you can sort the index() on a field in the 'Advanced' settings. This way you shouldn't need to re-sort the Viz to get the index going in the right direction.