2 Replies Latest reply on Jul 16, 2018 12:50 PM by Gary Bisaga

    Limit number of filter selections

    Gary Bisaga

      I need to limit the number of filter selections in the attached workbook, "Trends Dashboard". When the user selects too many towns (say, more than 3), he should get an error message. Here's the viz:

      I am familiar with the forum question here limit number of selections on multiple values quick filter . However, I've tried filtering on SIZE()<=3, COUNTD([Location])<=3, and COUNTD([Geography Label])<=3, and none of these work. In each case, the chart display remains. How do I fix this? And is there any good documentation on these table calculations? I have not found any. Thank you.

        • 1. Re: Limit number of filter selections
          Andy Piper

          Gary,

           

          I believe you can get it to work similar to how Jonathan Drummey suggested years ago. Per Jonathan's suggestions, I was able to evaluate the 'size' of the number of location choices and then swap worksheets when the number selected exceeded 3. See if the attached will help you out.

           

          Andy

          • 2. Re: Limit number of filter selections
            Gary Bisaga

            Andy, it looks like that worked! But I don't understand, I tried something very similar (also based on Jonathan's answer) and it did not work. Can you tell me, what is the SIZE() function doing here? My understanding of SIZE() is the total number of records in the current partition. However, there are a lot more than 3 records in the table. So, I edited the Table Calculation on the filter and found this:

            You have two changes from what I tried. One I think I understand and the other I do not:

            • You changed to Specific Dimensions and included all four dimensions, where mine did not include Location (which is of course the key dimension here). That one makes sense.
            • You changed "At the level" from the default of "Deepest" to "Location". This one I do not understand. Jonathan Drummey followed up on a related question here, but he basically said he didn't know what it meant.

            Can you explain what you did and specifically why changing "At the level" to "Location" made it work? Thanks!