3 Replies Latest reply on Jan 29, 2017 2:11 PM by Joe Oppelt

    Make something that looks like a quick filter, but does "AND" instead of "OR"

    Joe Oppelt

      I have been working with Loïc in this thread:


      how to display only combined categories on a map


      The initial question was about making two filter selections work like AND instead of OR.


      I made a suggestion that is easy to implement with two options, but the larger application would really require any number of selections from the filter (not just two).  And it will not always be a fixed number of selections.  Maybe two for one user, and four for another, etc.


      I have never really come across this question before.  I'm starting a new thread to begin the question fresh, with zero replies so that it gets new attention.


      An idea I can think of is to have a separate parameter for each possible individual item (in this case products.)  Let the user select Y/N for whichever is needed, and build a calc to accommodate those set to Y.  Obviously this would get messy as products change over time.


      Loïc attached a 10.1 workbook in that thread.  I reattached it here.  The question really isn't specific to the application or data contained in the application.  We could replicate it in Superstore, looking for all the [Regions] that sell ALL of the selected [Products].  But Loïc's workbook is attached here.


      Does anyone have any cool ideas for how to identify all customers who have purchased all of the selected products?

        • 1. Re: Make something that looks like a quick filter, but does "AND" instead of "OR"
          Joe Oppelt

          Forget it.  I just figured it out.



          Do one calc that figures out how many categories were selected.



          { exclude [Customer ID], [Product Categories]  : COUNTD([Product Categories]) }




          And another that does a COUNTD of products for the customer.


          If they are equal, that customer has all the selected products.  If not, filter out that customer.

          • 2. Re: Make something that looks like a quick filter, but does "AND" instead of "OR"
            Jamieson Christian



            I was so looking forward to getting home and writing up an answer to this question. You nailed it. Because we are blind to the filter settings themselves — we can only see the data that results from the filters — the best we can do is look at the unique values in the filtered data and assume that those unique values represent the items that were selected on the filter. There are a couple edge cases where this assumption may not hold up — a quick filter that is set to "All the Values in the Database" may show options that are not even represented in a view that has additional multiple filters applied, and so we may get the wrong idea about what was selected — but as long as caution is exercised, the assumption allows us to answer questions like the one posed here, as well as common questions like…


            1. Are any filters applied?
            2. Is only one option selected in a multi-select quick-filter?
            3. Has an action filter been applied? (A special case of either #1 or #2 above.)
            • 3. Re: Make something that looks like a quick filter, but does "AND" instead of "OR"
              Joe Oppelt

              Yup, Jamieson.  I actually use this to tell if a user has arrived at a dashboard by using the action filter, or by clicking on the tab.  If the number of COUNTD(whatever) is equal to FIXED :COUNTD, then likely they clicked the tab and not the action mechanism.  (And then I pop out a sheet that covers the whole dashboard which says, "You need to select a value from Main Menu to use this dashboard.  Click here to return to Main Dashboard.")

              I just didn't synthesize the principle for the question at hand in the original thread.