2 Replies Latest reply on Oct 21, 2016 9:09 AM by Joshua Milligan

    "IN" operator in calulation

    K P

      Hello all,

      I am looking for something like this calculation.

       

      if parameter = 1

      then

      columnName IN ("parameterValue1")

      elseif parameter = 2

      columnName IN ("parameterValue1","parameterValue2")

      else "All values of ColumnName".

       

      basically user can filter on 1 value OR 2 values or keep All values.

      I need parameter because we need to filter through URL.

       

      Is this possible?

       

      I know IN operator is not allowed in calculations but is there a workaround to achieve this result?

       

      any help will be appreciated.

      Thanks

        • 1. Re: "IN" operator in calulation
          David Li

          Hi there! Unfortunately, as you've noted, there's no IN keyword in Tableau. However, you have a few options.

           

          First of all, you could just use the OR keyword, e.g.

          columnName = "parameterValue1" OR columnName = "parameterValue2"

          Alternatively, you could do a neat trick with CONTAINS(). Specifically, you would search for your columnName value within a concatenation of your two parameter values. Something like this:

          CONTAINS("parameterValue1||parameterValue2||", [columnName] + "||")

          The reason for the double pipes (or it could be any unusual combination of characters) is to make sure that you don't get an internal match (e.g. matching "meterVal").

          • 2. Re: "IN" operator in calulation
            Joshua Milligan

            K P,

             

            I'm not entirely certain what you are working with, but I think it would be something like:

             

            (

            if parameter = 1

            then

                IF columnName = "parameterValue1" THEN 1 END

            elseif parameter = 2

            then

                IF columnName = "parameterValue1" OR columnName = "parameterValue2" THEN 1 END

            else 1

            end

            ) = 1

             

             

             

            There are a coupe of issues.  First, Tableau doesn't allow Booleans to be returned inside an IF statement.  Not sure why, but it is what it is.  So, I simply embed additional IF statements to give a result of 1 if the condition is met.  Then, I wrap the entire main IF statement in parentheses and compare the final result to 1 to get True or False result.

             

            Also, I took the IN statement and turned it into an OR (or series of ORs if needed).

             

            Hope that helps!

            Joshua