2 Replies Latest reply on Nov 16, 2017 4:40 PM by Dan Cory

# Using parameter to filter TRUE or ALL (No FALSE)

I just had a eureka moment and wanted to share.

For the longest time I've wanted to be able to filter for one value (eg TRUE) or filter for ALL values in the same dimension.  For example, using a dimension of the 50 States, I want to have a filter for a specific region (North East) otherwise show me all states, including the NE states.  You can think of this as grouping the NE states and the rest in an 'Other' bucket, or you can think of it as an if statement: IIF( [STATE] in ("MA", "ME", "NH", "NJ", "NY", "RI", "VT"), TRUE, FALSE).

I can throw that if statement as a filter and it'll give the selections of ALL, FALSE, TRUE.  But, this can be a bit cumbersome to use and it adds an additional selection (FALSE) that I don't want.

I always felt that there should be a way to filter for just TRUE or ALL (without FALSE), but I couldn't find any solutions online (if there is one, someone please point me to it) or figure it out, until just yesterday.  And it's simple; just needs a parameter and a calculated field.  Below is a walk through:

I'll use (and attached) an example with the 50 states.  Here are the steps.

1.) In the workbook, I've grouped the NE states as "1" and the rest of states are grouped in "0".

2.) Create a string value parameter with 2 values, "2" and "0".  Notice that I used "2" (any value other than "1" or "0" can be used) even though it doesn't exist in the states grouping.  Also, notice I displayed "NE Only" for the "0" even though "0" is the group for the non NE states.

3.) Create a calculated field with the argument: [State (group)] != [Parameter].  This field will be TRUE if the [State (group)] is NOT equal to the parameter value.  In other words, it will be TRUE for NE states when the "0" value in the parameter is selected because it makes the corresponding value of "0" in the [State (group)] to be FALSE.  Also, when "2" (All States) is selected, this calculated field will be TRUE for all values since there is no "2" in the [States (group)].

4.) Throw the calculated field as a quick filter, select TRUE, and VIOLA!

Hope you found this useful.

• ###### 1. Re: Using parameter to filter TRUE or ALL (No FALSE)

Hello George,

Could you please post a .twbx vs the .twb file attached here?  Also - feel free to add this to the Workbook & Calculation Library with commentary there.

Thank you

Patrick

• ###### 2. Re: Using parameter to filter TRUE or ALL (No FALSE)

You could probably use a Boolean parameter instead of a string. All would be true and NE would be false. Then your calculation would be [NE vs All] OR ([State (group)] = '1').

You could further use a set instead of a group and then your calculation would be [NE vs All] OR [NE Set].

My guess is this would optimize into the best query, especially if your NE set is quite small compared to the entire list of values. But as with any performance issue, you have to try it to be sure.

And it would depend on which version of Tableau you used as we've added some optimizations in this area in the last year.

Dan