3 Replies Latest reply on Aug 10, 2016 9:35 AM by Sarah McGraw

    Filters within filters....can this be done?

    Sarah McGraw



      I am attempting to create a calculated field based on a parameter, and then use that calculated field as a filter.....then use that calculated field in another filter. Can this be done? This is what I have so far:


      I have three worksheets.


      The first uses a parameter called "RJ_Currency_Filter" to designate data that I would like to format as currency. I then have a calculated field called" RJ_Dynamic_Currency" that sets the values specified in the parameter to the field "Code", which allows me to use the parameter control to filter on [Code]. (I also dropped the calculated field into columns.)


      The second is exactly the same, except the parameter is called "RJ_Integer_Filter", and it specifies a different set of values from [Code].


      The third is also the same, except now I'm specifying Percent data.


      What I want to do now is to somehow smash these three parameter controls into one master parameter control/ filter, then create a dashboard that swaps out the source graph depending on the choice (three source graphs, each with multiple code options). I can create a parameter/calc field duo that pull values out of the first three, but that doesn't seem to do anything.


      The final product I want is a single dash with a single drop-down that I can use to access any of the options from all three source worksheets. Help?


      Thank you!

        • 1. Re: Filters within filters....can this be done?
          Shinichiro Murakami

          Hi, Sarah


          I think I can support at some level, but if you can attach your packaged workbook (***.twbx), it's much easier to understand the issue.

          Could you?   Even only part of data is fine.




          • 3. Re: Filters within filters....can this be done?
            Sarah McGraw

            Hi Shin,


            Thanks so much for your offer of help, but I figured this out while away from my computer last night. I think I had just been staring at it too long. This was simpler than I had imagined (although hey, maybe there's a more complicated way to do this that might come in handy in other circumstances.) This is what I did:


            1) I created a parameter to specify all data I want to have available in my view, regardless of destination format--a list of subject codes from a field "Code" in my source. The codes are identifiers for a range of topically related measures, hence my desire to have them in the same dashboard.


            2) I created three different calculated fields, each drawing from a subset of the parameter codes, like this:

                                     CASE [parameter]

                                          WHEN "code_a" THEN [code]

                                          WHEN "code_b" THEN [code]




            All my elements draw from the same source field, as you can see--this is different than most examples I've seen of this strategy, which seem to use parameters to pull in data from different fields.


            3) I created three different workbooks, one for each calculated field. For each, I dropped the correct calculated field into the filter box and set it equal to the original parameter.


            4) I built correctly formatted graphs for each.


            5) I created a dashboard, and dropped all three worksheets into a container. Then I removed the worksheet titles. Now I can use a single parameter control to cycle through all 8 views--which are secretly three different worksheets.


            Now I need to pull data tables for each view into a second container controlled by the same parameter control, then pull titles and captions in from two more worksheets so the text can also dynamically swap out with the control. I don't think that will require any strategies that are substantially different from what I've already done here, though.


            And I need to figure out how to sync the x-axis alignment between the charts and the data tables.....that's my project this morning.