10 Replies Latest reply on Aug 7, 2018 3:28 PM by Prashant Mishra

    Inclusive Filtering

    Daniel Araya

      Hello,

       

      Does anyone know a way of making inclusive filtering?

       

      I have two columns. Column one is company name and column two is category. Some companies belong to more than one category, so in this case company name is repeated to another row and the categories change below. Something like this:

       

      CompanyCategory
      Aa
      Ab
      Ac
      Bc
      Ca
      Cb
      Cc
      Ce
      Cx

       

       

      If I add a "Category" filter with multiple value selection and I select categories "c" and "a", companies A, B, and C will be displayed because they all contain category c. I want only companies A and C to display because they contain the two categories I selected.

       

      In other words, I want the multiple selection in the filter to operate as an "AND" function, and not an "OR" function.

       

       

      I would appreciate your help SO much with this..

       

       

      Thanks,

      Daniel

        • 1. Re: Inclusive Filtering
          pooja.gandhi

          Hi Daniel!

           

          You could do something like this maybe:

           

           

          Basically first count the categories selected and window_max tells Tableau to show 'true' only if a company has the max count of categories (selected in the filter). Drag this calculation to filter and select 'true'. So now if you select categories 'a' and 'c' only companies 'A' and 'C' are visible.

           

          Hope this helps!

           

          Pooja.

          • 2. Re: Inclusive Filtering
            Daniel Araya

            Hello Pooja,

             

            This is extremely helpful. I did not know WINDOW_MAX existed. I was kinda looking for it.

            But now I run into another problem. If I have a category z, which companies A and C do not have, but others do.. I want no companies to appear at all, because no companies will have that combination of the 3 selected categories. But other companies have categories a and z, but not c, so the max count, if they have a and c, or a and z, are the same (2) and all companies with at least 2 of the keywords are appearing, when 3 keywords are selected. No companies should appear if no combination of the 3 keywords exist.

             

            Can you think of a way around this?

             

            Thanks again for your help! Extremely smart!!

             

             

            Best regards,

            Daniel

            • 3. Re: Inclusive Filtering
              pooja.gandhi

              Daniel!

               

              Ok, sorry about that. Should have thought about that earlier. Here is a workaround, if thats the case. If you are using Tableau verion 9.0 or higher, you can utilize the very powerful level of detail (LOD) expressions:

               

              IIF( COUNTD([Category]) = sum({ exclude [Company] : countd([Category]) }),'True','False')

               

              With this I am telling Tableau to exclude company and just countd number of categories selected in the filter and hence forgoing the window_max expression which may be situational based on the selection like you described above. And then the countd(category) is just counting the categories per company based on selected members in the filter. If them 2 equal, then true else false. Dragging it to filter and selecting true would give your desired results, unless something else comes up with your real dataset

               

              So when 'a' and 'c' are selected, we are retaining our previous result:

               

               

              And when 'a', 'c' and 'z' are selected we have a blank view:

               

               

              Hope that helps.

               

              Pooja.

              1 of 1 people found this helpful
              • 4. Re: Inclusive Filtering
                Daniel Araya

                Pooja,

                 

                This is genius! Thanks so much!! The values displayed (true/false) are exactly what they are supposed to be. But I am not being able to drag the calculated field into my filters. Why might this be? I'm using 9.0

                 

                Thanks again! Almost there

                 

                Best regards,

                Daniel

                • 5. Re: Inclusive Filtering
                  Daniel Araya

                  I added it to columns, and selected to "hide" false values, but I'm still confused as to why it won't allow me to properly filter like you did.

                   

                   

                  Thanks again,

                  Daniel

                  • 6. Re: Inclusive Filtering
                    Daniel Araya

                    Hello again Pooja,

                     

                    Sorry for the spam.

                     

                    I'm sorry for not mentioning this before, but after this, I need to display data from a different source connected by the company name. When I'm doing this the value for the "selected categories in the filter count" gets messed up. Because it's not excluding the new columns I'm adding. Any chance it's possible to reference columns from another data set in that exclusion as well? Otherwise I'll add them to this data set and exclude them properly in the calculated field, but this would make my document run much slower since some columns are very text intensive.

                     

                    Thanks for all your help. You're awesome!

                     

                    Best regards,

                    Daniel

                    • 7. Re: Inclusive Filtering
                      Amanjot Klair

                      Hi Daniel,

                       

                      I am not perticularly aware of the problem but if you are looking to add just a column from a different data source it is fairly possible. You can add the field from the other source into your calculation but its not possible to use in the LOD calculation in v9. I couldn't get my hands on to v9.2 yet but here is a post where this question has been answered earlier as well.How can I include a LOD calculation from secondary data source?

                      • 8. Re: Inclusive Filtering
                        pooja.gandhi

                        Hi Daniel!

                         

                        Well, I am glad that worked! You can not add fields from multiple data sources in the LOD expression. If it is possible, I would recommend adding the data to your primary dataset and use LODs on that source to keep things clean. Level of Detail calcs are row level, when you blend a data source, the secondary source you are connecting to is a pre-aggregated source, hence it doesn't allow fields from a pre-aggregated source to be added on to a row level calc expression of the primary source.

                         

                        Another way may be to join the datasets instead of blending which would allow you to use the fields from both the sources, but ofcourse it depends on what your end goal is. Regardless, let me know how you fair with it

                         

                        Pooja.

                        • 9. Re: Inclusive Filtering
                          Prashant Mishra

                          Hi Pooja Gandhi,

                           

                          I am facing similar kind of issue. I am able to use the calculated field in the filter pane and set its value as "TRUE". This is giving me all the names based on inclusive filtering of the Value.

                           

                           

                           

                          I was wondering if I can find the distinct count instead of the actual names. When I try to get distinct count, I am getting the incorrect output (6) as shown in the below figure. I am expecting an output to be 3 since there are only 3 names in the inclusive filtering.

                           

                          Could you please help me to solve this issue.

                           

                          Below is the data.

                           

                           

                          NameValue
                          TomA
                          HarryB
                          MonicaC
                          HenryD
                          KapilE
                          SuyashF
                          PrashantG
                          TomB
                          HarryD
                          MonicaD
                          HenryA
                          TomE
                          HarryG
                          KapilF
                          SuyashA
                          PrashantA
                          TomD
                          MonicaE
                          HenryB
                          KapilC
                          SuyashC
                          PrashantB
                          TomG
                          HarryA
                          MonicaF
                          HenryG
                          KapilD

                           

                          Best,

                          Prashant Mishra