7 Replies Latest reply on Oct 31, 2018 12:18 PM by Joe Oppelt

# Can we specify value of "ALL" option in filter selection?

For example, if I have a filter (Airport Filter) and I have a measure (Target Enplanements), within the measure my formula is

IF [Airport Filter] = "Airport 1"

THEN Target1

ELSEIF [Airport Filter] = "Airport 2"

THEN Target2

END

Then based on the user's filter selection, I want to change the Target Enplanements that is displayed. The measure formula I have above works.

However, I also want to add....

IF [Airport Filter] = "Airport 1"

THEN Target1

ELSEIF [Airport Filter] = "Airport 2"

THEN Target2

ELSEIF [Airport Filter] = "ALL"

THEN Target1 + Target2

END

Which does not have an effect on the "ALL" selection inside of the filter.

Is there any way I can achieve a similar effect? I want to reflect a sum of Airport 1 and Airport 2's targets when the user selects "ALL"

Thank you for your help!

• ###### 1. Re: Can we specify value of "ALL" option in filter selection?

"ALL" is not an actual value in the filter.  It's a convenience that tableau provides to the user so that it's not necessary to do 100 individual clicks if there are 100 items in the list.

You will have to calc a way to detect if ALL items are selected.

This calc:

{ FIXED : COUNTD([Airport]) }

... will tell you how many airports are in the data source.

This calc:

{ EXCLUDE [dimension1](, [dimension2], ..., [dimension-N]) : COUNTD([Airport]) }

... will tell you how many airports are selected on the sheet.

If they are equal, then the user has selected all.

Note:  in the second calc, you want to make an EXCLUDE list of all the dimensions on the sheet, separated by commas.

• ###### 2. Re: Can we specify value of "ALL" option in filter selection?

It can be achieved using parameter. Let me know if you're okay with parameter. However, I think it can be achieved with filter also  using lod and count but have to check.

• ###### 3. Re: Can we specify value of "ALL" option in filter selection?

Joe Oppelt you beat me to it

• ###### 4. Re: Can we specify value of "ALL" option in filter selection?

It depends on how you have your sheet set up.

Screenshots don't help.  If you need help, I'll need a sample workbook.

• ###### 5. Re: Can we specify value of "ALL" option in filter selection?

PS:  Will you have only two choices (besides ALL) in your filter list?

And if you have more than 2, will you allow your user to select more than 1?

• ###### 6. Re: Can we specify value of "ALL" option in filter selection?

Yes, I will only have two choices (beside ALL) and the user is allowed to select more than 1.

Unfortunately, I won't be able to share my workbook :-( I appreciate your help though!

• ###### 7. Re: Can we specify value of "ALL" option in filter selection?

Hack up a sample using superstore.

Or anonymize your workbook and data to make a subset of your workbook.  (  See:  Video demonstrates how to anonymize your workbook/data )

I don't need all your columns.  Just a few rows with enough dimensions to demonstrate what you have, and maybe one bogus measure.

There are so many variables in what I could describe for solutions that we could go around forever trying to guess what your sheet looks like and what you're trying to do.

If you have only 2 possible choices (plus ALL), then you can control this easily with a parameter.

You're getting two marks on the sheet because you have Airport somewhere on the sheet, and tableau is making one mark per airport.  We can eliminate that if we approach this with a parameter.

My original reply assumed that the filter could have any number of values, and that some multiple number of values (less than all values) could be selected.  It's very straightforward if you only have up to two choices.