6 Replies Latest reply on Sep 19, 2018 1:25 PM by Michael Marcavage

# Filter Selected Items and Available items

I have a fill map map with filters exposed to the user.

If I have a filter of countries based on the selected state, in a calculated field:

1) How would I get the number of options Available?

2) How would I get the selected items?

If state DE is selected, there are 4 counties, NJ lets say 30 counties.

I need a way to determine how many counties are Available and of those counties how many are selected.

Is there a way to do this?

(Sorry I am not permitted to post my viz)

• ###### 1. Re: Filter Selected Items and Available items

You can hack up a similar example in Superstore.

Or you can follow the steps demonstrated in the video linked here:

Sometimes it's easier to show you than to describe it to you.

But for starters, a calc like this would tell you how many counties are available for any state:

{ FIXED [State] : COUNTD([Counties]) }

and a calc like this would tell you how many counties were actually selected:

{ EXCLUDE [dimension 1] (, [Dimension 2], ... [all the dimensions on your sheet except for State] ) : COUNTD([Counties]) }

Note:  I typed that off the top of my head.  I would really need to play with it and test it out to be sure it was accurate.

• ###### 2. Re: Filter Selected Items and Available items

Sorry, I am not following what you are referring to when you listed:

{ EXCLUDE [dimension 1] (, [Dimension 2], ... [all the dimensions on your sheet except for State] ) : COUNTD([Counties]) }

• ###### 3. Re: Filter Selected Items and Available items

That's why it helps to have a sample workbook to work with.

if you have 5 dimensions on your sheet, and one of them is [State], you would list the other 4 after EXCLUDE in that syntax.

• ###### 4. Re: Filter Selected Items and Available items

Not sure that is what I am looking for.

If in the State filter I select DE, The County filter will change to show the 3 counties in DE.

If the filter is a multi select drop down and the user selects 1 or 2 of the available options, I am trying to capture that.

This will allow me to do another calculated field to compare what is available vs what was selected.

Does that make better sense?

• ###### 5. Re: Filter Selected Items and Available items

See attached.  (When I suggested that you provide a sample workbook, this is what I was referring to.  Just hack up something with the same sort of behavior.)

In this workbook I have a calc that tells me cities per state in the data.  (See Sheet 3.)

On Sheet 4 there is another calc (displayed in the title) that tells me how many were actually selected.

So now you can check if one calc is equal to the other.  If not, they have selected something less than all the cities available.

• ###### 6. Re: Filter Selected Items and Available items

I see how you did this, you EXCLUDED all of the values that were listed in the Columns section. I only have Longitude (Generated) listed there