5 Replies Latest reply on May 22, 2014 5:15 AM by Matt Lutton

    Filter two fields at the same time

    Kiril Zlatkov

      Hello,

       

      I have a data model setup where I need to be able to filter two fields in different tables with only one Quick Filter. Can anyone help? Below I provide detailed explanation on the model and what I am trying to do, as well as, a sample workbook.

       

      I have a list of real estate properties and each one of the has certain attributes. For example, the first 4 rows are:

       

      PropertiesM Table

      PROPERTYM

      TYPEM

      LOCATIONM

      USER_ATTR1M

      USER_ATTR2M

      USER_ATTR3M

      VALUEM

      Property1

      Office

      New York

      Blue

      Big

      Furnished

      19.6349

      Property2

      Office

      LA

      Red

      Small

      Not furnished

      6.6126

      Property3

      Retail

      Boston

      Green

      Medium

      N/A

      4.7441

      Property4

      Retail

      New York

      N/A

      N/A

      Furnished

      4.4807

       

      I also have a table that contains the covariance of each Property with all other properties. For example:

       

      CovarNormalized Table

      PropertyM

      PropertyN

      Value

      Property1

      Property1

      1. 307.389

      Property1

      Property2

      1. 200.242

      Property1

      Property3

      1. 148.788

      Property1

      Property4

      1. 145.018

       

      In fact the reason, my table with property attributes is duplicated is that it allows me to lookup attributes of each property mentioned on a row in the CovarNormalized table. For example, the second row in the CovarNormalized table is:

       

      Property1

      Property2

      1. 200.242

       

      What I need to be able to reference in my formulas is the Location of “Property1”, which is New York, as well as the Location of “Property2”, which is LA. I also need to reference the VALUEs of each property, which are 19.6349 and 6.6126 respectively.

       

      My ultimate goal is to have a filter on Location, which should filter the CovarNormalized table in such a way as to keep only Properties that are in the selected locations. For example, if I filter on New York and LA, what I am left with in the CovarNormalized table is:

       

      CovarNormalized Table

      PropertyM

      PropertyN

      Value

      Property1

      Property1

      1. 307.389

      Property1

      Property2

      1. 200.242

      Property1

      Property4

      1. 145.018

       

      This is the reason I have made two copies of my Properties table, which are called PropertiesM and PropertiesN. It allows me to have two separate links to the table CovarNormalized, one on PropertyM and one one PropertyN.

       

      This leads me to my question. How can I have only one Location filter, that simultaneously filters PropertiesM.LOCATIONM and PropertiesN.LOCATIONN?

       

      Any help is greatly appreciated.

       

      Thanks,

      Kiril

        • 1. Re: Filter two fields at the same time
          Ganesh K
          Hi Kiril Zlatkov,

           

          Here is few steps need to be made:

          1. Select both PropertyM and PropertyN using Ctrl Key right click Combine fields.


          2. To the Row shelves Drag:

          *) PropertyM, PropertyN, PropertM,PropertyN(Combined field), Location, and ValueM .

          Note: Location in row field is just for your reference only, you can remove it later.


          3. Now drag Location field to the filter just select LA and New york and also Check mark on exclude option, so you will be getting excluded data (i.e. other than LA and New york).

           

          4. Now you will be getting the view which you required. At last Go to Row field  "PropertM,PropertyN(Combined field)"

          Right click "Show Header" need to be uncheked.

           

          Let me know if this is what you want?

           

          Regards,

          Ganesh K

          • 2. Re: Filter two fields at the same time
            Kiril Zlatkov

            Hello Ganesh K,

             

            Thanks a lot for your response and apologies for my slow reply. I was away for a few days.

             

            I am not sure that what you are suggesting achieves my objective. I followed the steps you outlined and was not able to filter both the LOCATIONM and the LOCATIONN field. Also, I want to clarify that the filter of the two fields LocationM and LocationN should work independently for each field without setting them equal to each other. For example, if I filter for two cities (i.e. Boston, New York) the results I would get if the fields are set equal to each other are:

             

            LOCATIONM

            LOCATIONN

            Boston

            Boston

            New York

            New York

             

            However, what I am actually looking to get is the following:

             

            LOCATIONM

            LOCATIONN

            Boston

            Boston

            Boston

            New York

            New York

            Boston

            New York

            New York

             

            Essentially the filter should function like an IN statement in SQL:

             

            WHERE LOCATIONM IN (‘Boston’, ‘New York’) AND LOCATIONN IN (‘Boston’, ‘New York’)

             

            Sorry if I was not very clear in the beginning and I hope this information helps. Looking forward to hearing from you and I appreciate your help with this.

            • 3. Re: Filter two fields at the same time
              Ganesh K

              Hi Kiril Zlatkov,

               

              Please find the attachment, i have worked on it please let me know if this works

               

              Regards,

              Ganesh K

              • 4. Re: Filter two fields at the same time
                Kiril Zlatkov

                Hi Ganesh K,

                 

                Once again thanks for your response. To make sure I understand correctly, the way your solution works is that the user would need to independently filter the values for the two fields (LocationM, LocationN), but for the first field they select what's in and for the second they select what's out?

                 

                My objective is to have the user work with only one filter. Imagine that there are dozens of Locations that need to be selected/deselected. Having to do that twice is not very user friendly.

                 

                Regards,

                Kiril