3 Replies Latest reply on Nov 14, 2018 1:40 PM by Peter Fakan

    How to filter a dimension while keeping one of its values constant

    chia-hua.yu

      Sorry for the horrible title, but it's hard to make it a one sentence question.
      Suppose I have a dimension I want to filter on called measure. Also suppose that I have a few demographic dimensions,  for example Race and Language. For a given measure, not all race values or language values may be present—however, there is one measure that is in essence a dummy measure that does have all the values.

      I would like to be able to somehow leverage the FullCube measure to provide the "missing values" of Race, Language, and other demographic dimensions. In the attached example, there's a "Workout Language" tab that shows all measures with the language dimension in place. I would like to be able to have a dropdown that allows me to filter down to 'FullCube' and one other measure, so I can use a LOD to "borrow" the FullCube rows, if that makes sense (ending up with a view like "Final View Language"). The hope is that I can have a single dropdown that allows me to filter down multiple charts in this manner.

       

      Constraints:

      I thought about using a parameter to provide a filter, but our current dashboard requires features that only quickfilters provide (find as you type, and "show only relelvant values").

      I have also thought about a left join, but I have 4 or 5 demographic fields, and our dataset is 900K rows so performance is a concern. We were hoping to avoid having to create that left join.

       

      Thanks for reading!

        • 1. Re: How to filter a dimension while keeping one of its values constant
          Peter Fakan

          Hi Chia,

           

          I'm not 100% sure of your requirements but is this the kind of thing you are looking for ?

           

           

          HTH 18.3 attached

           

          Peter

          • 2. Re: How to filter a dimension while keeping one of its values constant
            chia-hua.yu

            Thanks for having a look Peter Fakan!

            I had a look at your twbx and saw that you added the following calc:

            if [MED_AVDEDU]==[Full Cube] then [MED_AVDEDU]

            else [Full Cube]

            END

            This does provide part of the functionality that I want, which is to "back-fill" in values where MED_AVDEDU doesn't exist (in my workbook I use an LOD to accomplish something similar).

             

            However, the other wrinkle is that I need to be able to switch out MED_AVDEDU for other measures. This would be trivial if I could use a parameter, but unfortunately I cannot use parameters because of the following 2 requirements that parameters can't fulfil:

            • I need a single select dropdown menu that allows find-as-you-type to narrow the dropdown (currently only available for quickfilters, not parameter dropdowns)
            • In my production dashboard, I have category filter dropdown that further helps to narrow the choices. In the production dashboard there are something like 100 different choices, so being able to narrow the selection is key.
            • 3. Re: How to filter a dimension while keeping one of its values constant
              Peter Fakan

              Hi Chia,

               

              What you are asking is far too complex to achieve without using the full functionality of Tableau. By limiting your choices to quickfilters only, you are limiting the solution. Does your organisation also use Word but not allowed to change the font or font size ?

               

              My initial thoughts on your most recent post is there is a slim chance this might work (depending on your data and what you are doing with it) if you replace your quick filters functionality with selectable sheets - the idea behind this is looking at narrowing the choices as your key functionality. I've mocked up a quick example using superstore data to show you what I mean. I didn't have time to find an example in the data where the filter will 'change' based on the first selection, but this will work in practice (i.e. if 'Home Office' was selected but there were no 'Standard Class' items, then the 'Standard Class' option would disappear).

               

              HTH 18.3 attached

               

              Peter