4 Replies Latest reply on Jan 24, 2019 9:53 AM by Linda Dawson

    Dynamic active filter window based on another filter value

    Linda Dawson



      When I select Office Supplies or Technology in the category active filter window, only the sheet is filtered, nothing else happened.



      But when I select Furniture in the Category filter, I want that the Region active filter window also showed. Is it possible?


      I mean the Region active window will be shown or not shown based on the Category filter value.







        • 1. Re: Dynamic active filter window based on another filter value
          Joe Oppelt

          You can pop out the Region filter if Furniture is selected.


          You're doing this in Superstore.  So you can share what you have.  Upload your packaged workbook and I'll show you.  (Just telling you will be wordy.  It's far easier to show you.)

          • 2. Re: Dynamic active filter window based on another filter value
            Linda Dawson



            Please see the attachment for the workbook.





            • 3. Re: Dynamic active filter window based on another filter value
              Joe Oppelt

              In the attached I made space for the filter to pop out by shoving a blank after the Category filter.  That's where Region will show up when it should.


              Go to Dashboard 2(2).


              I made a sheet that displays or hides based on whether or not "Furniture" was selected.  See "Pop out Region Filter".  In that sheet I have a filter.  It displays the sheet if [Category] = "Furniture".  Play with the filter when you are editing the sheet and you'll see what I mean.  I made a bogus calc, [A], just so that I have something on Rows or Columns.  It can be anything.  This sheet can actually display anything.  It will never be seen by the user.  The point is to have a sheet that displays only under certain conditions. (In this case, when "Furniture" is selected.)  Note too that I mace the Category filter apply to both your original sheet and this pop-out sheet.  That way the pop-out knows what was selected for the data sheet.

              Next on Dashboard2(2) I displayed the Region filter for Sheet 3.  And I made a floating vertical container into which I dragged the pop-out sheet and the Region filter below it.


              On dashboard2(2), play with the Category filter.  You will see the Region filter move up and down as the pop-out sheet displays or hides.  That's called Popping.


              Now go to Dashboard2(3).  Last step:  I placed the starting y-coordinate for the container to a negative number.  As long as the pop-out sheet is bigger than the object it is pushing (in this case the filter), it will push the filter into view on the dashboard.  I settled on -250 just by playing with the number until the whole Region filter came into view when the pop-out was expanded.


              This technique is described in a series of videos for swapping sheets and popping objects.  You'll find them here:


              New series of videos on swapping and popping on a dashboard


              What I did here is covered in video #2.


              And you may need some of the other stuff too, and here's why.  When  Furniture is selected, and if the user selects some regions, if they subsequently go to "Technology" without resetting the Region filter to ALL, that filter will still remain active, and the user will only see data for Technology in the selected regions, even though the filter is hidden.  And if they decide they want ALL regions, they can't get to that filter unless the re-select Furniture.  (Make sense?)


              Therefore you may want to swap two sheets for Sheet 3.  One will display if "Furniture" is not selected, and it will not have the Region filter on it.  The other will display if Furniture is selected, and the Region filter will display.  If you need that, we can discuss that too.  (The series of videos address a scenario somewhat like this.  And for the example as you've described it, Videos #1 and #2 together would cover what you need.)


              One last thing.  The [Furniture?] calc I have in here is simplistic and assumes a single select Category filter.  It would not work properly if you have a multi-select filter and want to display the Region filter if Furniture is among any of the selections.  But we can make that happen if you need it.  Likewise, it will not work in its current state if the user selects ALL in the filter.  Or, at least, it will not find "Furniture" as one of the full set of Categories.  Again, we can make it find "Furniture" from among ALL if needed.


              But for now I have demonstrated how to display the Region filter conditionally.  From here it's a matter of deciding what your application will really need and how you want the dashboard to function.

              1 of 1 people found this helpful
              • 4. Re: Dynamic active filter window based on another filter value
                Linda Dawson



                Amazing! Though I need some time to totally understand what you mentioned here, I find that the attached workbook really works. I will watch the video about the popping carefully, and hope to fully master this skill and use in my current project.


                Thanks for your time to write the details step by step. I am very grateful for your help!