10 Replies Latest reply on May 25, 2016 10:28 AM by Tom W

    Creating Filter from multiple dimensions.

    B Scott

      I have 4 dimensions from an SQL Server Database. What I want to do is combine them and make a filter with it.

       

      Example: Dimensions A, B, C and D. They all either have 1 or 0. I want 4 checkboxes named A B C and D (named after the dimensions) and when they are checked I want the 1s of the dimension I checked and when unchecked the 0s.

       

      So far I have tried combining into one calculated field but I am left with every permutation of the dimensions in which they can be true or false. Such as "True True True True, True True True False" all the way to "False False False False" which I do not want.

       

      I want to know if there is a way to do this on tableau or will I have to revise the query in SQL Server.

        • 1. Re: Creating Filter from multiple dimensions.
          Tom W

          It sounds like a standard filter should work just fine. If you drag each dimension into the filter pane, you can enable the quick filter for each. This would allow you to have 4 dropdowns to change each to different values.

          Have you tried this?

          • 2. Re: Creating Filter from multiple dimensions.
            B Scott

            This is what I currently have. One for each dimension and whether or not they are 1 or 0(Yes or No).

             

            But what I actually want is to have 1 dropdown. That I can check or uncheck and the check represents Yes and Unchecked means No.

            • 3. Re: Creating Filter from multiple dimensions.
              Tom W

              Create a parameter with the type integer with two values a Yes (1) and No (0).

              Then the question becomes, how are you applying this? As I see two ways;

              Method 1;

              • Select 'Yes', only select records which = 1 for all four categories
              • Select 'No', only select records which are not exactly 1 for all for categories / have at least one zero for a category.

              Method 2;

              • Select 'Yes', only select records which = 1 for all four categories
              • Select 'No', show everything, it doesn't matter what the category combination equals.

               

               

              For Method 1;

              • Create a parameter, we'll call it 'ShowOnes', datatype string with two values, Yes and No
              • Create a calculated field called FilterField with the formula; IF [Category A]=1 and [Category B]=1 and [Category C]=1 and [Category D]=1 then 'Yes' else 'No' end
              • Drag FilterField onto the filter shelf, click 'Use All' on the general tab, then click condition , select By Formula and enter [ShowOnes]=[FilterField]
              • Right click your parameter and select 'Show parameter control'
              • 4. Re: Creating Filter from multiple dimensions.
                B Scott

                I need to have it where I can alternate between all 4 possible combinations with checkboxes. All this would do is filter out things between having all 1s vs. every other combination.

                • 5. Re: Creating Filter from multiple dimensions.
                  Tom W

                  Alter the method to use parameter which contains a value for each of the combinations and setup the formula appropriately. It's not a huge leap from the examples provided above.

                  • 6. Re: Creating Filter from multiple dimensions.
                    B Scott

                    That creates a list of every single combination, as opposed to checkboxes for the choices.

                    • 7. Re: Creating Filter from multiple dimensions.
                      Tom W

                      As I suggested in my first reply, setup a quick filter for each field then.

                      This will give you a check box for each of the four dimensions.

                       

                      I'm not following how that doesn't give you what you need, it seems to be

                      exactly what you're asking for?

                      • 8. Re: Creating Filter from multiple dimensions.
                        Tom W

                        Can you literally draw a mockup of how you want the filter to look with the possible values?

                        • 9. Re: Creating Filter from multiple dimensions.
                          B Scott

                          Similar to this:

                          Untitled.png

                          Not this:

                          all permutations.JPG

                          The only thing is the options from the first image should be Category A, Category B, Category C, and Category D. But the filter should look like that.

                           

                          The options from the second image are all combinations of what you could get, as a row can be in multiple categories at the same time.

                          • 10. Re: Creating Filter from multiple dimensions.
                            Tom W

                            You would need to use a parameter for this based on a list, but you cannot do a multiple selection parameter, it's not possible.

                            You're either limited to four individual parameters / quick filters like I mentioned earlier or you would need to restructure your data in a way which would allow you to have a table like;

                             

                            Animal, Dimension, Value

                            Koala, Dimension A, 1

                            Koala, Dimension B, 0

                            Koala, Dimension C, 0

                            Koala, Dimension D, 1

                            Kangaroo, Dimension A, 1

                            Kangaroo, Dimension B, 0

                            Kangaroo, Dimension C, 0

                            Kangaroo, Dimension D, 1

                             

                            Then you could use a quickfilter on the Dimension field and build the filter as per the screenshot sample.