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

    Using parameter to filter TRUE or ALL (No FALSE)

    George Kwong

      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.