4 Replies Latest reply on Nov 17, 2016 5:27 AM by Mark Holtz

    Filtering a dimension on a dimension against a measure

    Kerry Page

      I know this has to be super easy but I am at a loss in figuring this out. Help appreciated.  Can not attach my workbook due to confidentiality.

       

      Data:

      Column = Name (around 400 names)

      Column = Vendor Type (there are 3)

      Column = $Total Cost

       

      Looks like this:

      John Smith     VendorA   $10  

      John Smith     VendorB    $15

      Jane Smith     VendorA    $10

      Jane Smith     VendorC    $60

      Lucy Smith     VendorC     $14

       

      When I show all of the Names and select my filter on Vendor Type A and Vendor Type B, it seems to read the filter as any Name in the data that has VendorA OR VendorB, and displays that.

      I want to create a filter that only pulls Names that have VendorA and VendorB, or VendorB and VendorC, etc., or all 3 vendors, or just one vendor that's based on the Name field.

       

      In a nutshell, I want to visually show which Names are using more than one Vendor, which Vendor it is, and how much it costs.

       

      Can I change my 'Vendor' dimension filter to read it as an 'and' statement instead of 'or' on the 'Name' dimension?

      Do I need to create a parameter and if so, how can I create a parameter with multiple selections?  Can't find this anywhere.

      Or do I need to do an if/then statement and how do you do an if/then statement that pulls all of the rows with the same Name and different Vendors?

       

      Thanks.

        • 1. Re: Filtering a dimension on a dimension against a measure
          Mark Holtz

          Hi Kerry,

           

          If I'm understanding what you're after, there are a few approaches to get there.

          You could create a set of Vendor A and Vendor B and then pull it to the filter shelf to show the "Included in Set" members only.

          I believe you could also create a group.

          I must confess I don't often make use of those features, which are probably the easiest way to do this.

           

          You could also write a calculated field that would hard-code logic (and would automatically update given the criteria you specify). This is my preference.

          Something like:

          [Special Vendor Filter]:

          IF CONTAINS([VendorName] ,', LLC' ) THEN 'LLC' ELSE 'Other' END

           

          Then drag you new field [Special Vendor Filter] to the filters shelf and check to include the LLC value only.

          Then if you have
          Vendor A, LLC
          Vendor B, LLC
          Vendor C, Inc.

           

          You would only get A and B with your filter.

          And if later your data refreshes and suddenly now has Vendor D, LLC--you'll get A, B and D with the filter.

           

          Obviously, you must ensure the test you perform is not going to be "satisfied on accident"

          Like if you had some Vendor named "A,LLC ohol", it would satisfy the test. But if you make sure the logical test of the CONTAINS function is specific enough, that should get you what you need.

          • 2. Re: Filtering a dimension on a dimension against a measure
            Kerry Page

            Hi Mark - thanks so much for the response and suggestions.  I tried creating a set, called VendorA/B and included Vendor A and Vendor B then put it in the filter to only show 'In' set.  It showed me all names that had either Vendor A or Vendor B but did not filter out only the names that included Vendor A and vendor B.

             

            Then I tried creating a calculated field:

            IF CONTAINS('Vendor A, Vendor B', [Vendor Name]) THEN 'Vendor A/B'

            ELSEIF CONTAINS('Vendor A, Vendor C', [Vendor Name]) THEN 'Vendor A/C'

            ELSEIF CONTAINS('Vendor B, Vendor C',[Vendor Name])THEN 'Vendor B/C'

            ELSE 'Other' END

             

            When I dragged this to the filter shelf my only option was 'Other' and it only showed 'Other' for all of the names.  Somehow it is only looking at the individual row, which only has 1 vendor name, so it only recognized that but is not able to group multiple rows with the same name but different vendors. I'm sure it can...it's just me.

             

            Thanks again.

            Kerry

            • 3. Re: Filtering a dimension on a dimension against a measure
              Kerry Page

              One more thing...The article that has at least gotten me somewhere with this is this one:

              Getting the Total for Count Distinct | Tableau Software

               

              By adding Name+Vendor and turning it into a Distinct Count and an Attribute at least counts the number of vendors for me...it is in the ballpark of what I'm trying to do but looking for a better way to group them instead of counting a string of names+vendors.

              • 4. Re: Filtering a dimension on a dimension against a measure
                Mark Holtz

                Hi Kerry,

                 

                Sorry for the delay, but if you're still stuck on this, I'll try to help. The syntax of CONTAINS(string,substring) is such that it will look IN the string expression for the substring value.

                 

                You said you set your formula to IF CONTAINS('Vendor A, Vendor B', [Vendor Name]) THEN 'Vendor A/B'

                so you were telling it to find [Vendor Name] inside the static string 'Vendor A, Vendor B'.

                 

                That will likely never succeed.

                CONTAINS is not intended to test 2 values simultaneously.

                 

                I would set it up like this:

                IF CONTAINS([Vendor Name],'Vendor A') = True AND CONTAINS([Vendor Name],'Vendor B') = True THEN 'Vendor A/B'

                ELSEIF CONTAINS([Vendor Name],'Vendor A') = True AND CONTAINS([Vendor Name],'Vendor C') = True THEN 'Vendor A/C'

                ELSEIF CONTAINS([Vendor Name],'Vendor B') = True AND CONTAINS([Vendor Name],'Vendor B') = True THEN 'Vendor B/C'

                ELSE 'Other'

                END