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

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

    Sarah McGraw

      Hello,

       

      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.

           

          Thanks,

          Shin

          • 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]

                                          etc.

                                     END

                                   

            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.