3 Replies Latest reply on Jul 2, 2018 1:36 PM by Jim Dehner

    Dynamic, non-unique filter (multiple variables)

    Richelle Russell

      I have a workbook where I'm trying to create a filter that refers to multiple variables. We have school facilities where they might serve students in multiple grade bands (i.e. elementary, middle, high), and I need a filter that will allow me to include a school in both elementary and middle, if they serve both those grades, depending on whether "Elementary" or Middle" is selected.


      I've accomplished this before by using sets and a parameter (see packaged workbook).  But this data set will be updated yearly, and I'd like to not have to redo my sets each year. I've tried to mess with dynamic sets, but it seems as though it has to be an aggregated value and I don't think my data is set up to do that. I almost had it, but most schools exist year over year, so their name appears for each school year, but their grade span might change. So when I had a school that was only Elementary for years 1 and 2, but then started also serving Middle, my dynamic set was including them in the Middle set for all three years.


      The attached workbook is a much simper version of the larger workbook. More than just enrollment is being aggregated (counts, capacity, etc). Some of the data is also being aggregated and displayed on a map.

        • 1. Re: Dynamic, non-unique filter (multiple variables)
          Anuvir Singh

          Can you give an example of the answer you are expecting if possible (narrow it down to filter condition,year and school level) ?

          • 2. Re: Dynamic, non-unique filter (multiple variables)
            Richelle Russell

            I need to be able to filter by grade band and aggregate values for just those schools that are within that grade band (i.e. total enrollment for all Elementary schools, or total capacity for all Middle schools), but in a way that doesn't require me to update my sets each year.


            I have another filter that is also by school year, but that filter isn't a problem since it is one dimension with a unique value for each school.

            • 3. Re: Dynamic, non-unique filter (multiple variables)
              Jim Dehner


              Not certain what you expect - the data is a bit confusing - specifically there are several columns for enrollment that don't seem to be directly related to elementary or middle - ie sometime it is a single value repeated other times it is aggregate


              here is what the model does - you want to use the same values in 2 different ways - counted as elementary and counted as middle

              so I unioned a copy of the data upon itself


              then in creating the viz you use one sheet for elementary and the other for middle


              and then the same for labels


              then you can use those values in any viz - just an example -





              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.