How do I create a variable with a logical function like IN or INLIST in Tableau?
- Tableau FAQs
- December 11, 2018
Tableau does not have an IN or INLIST function, so that you can use CONTAINS or STARTWITH. This is a fairly common and useful functionality, and it is used to filter data at the table level and extract only the required value from the field.
For example, we need to get ProductIDs that contains the number 7; then we could use the following expression:
IF CONTAINS(STR([ProductID]), '7') THEN ProductID ELSE NULL END
IF CONTAINS(STR([ProductID]), ‘7’) – since the data in the ProductID field is stored in the INT format, we need to convert this format to text because the CONTAIN function only works with the text format. The CONTAIN function has two required arguments and the first is the string in which to search for the text, and the second is the text to search for. This expression evaluates to TRUE or FALSE.
THEN ProductID – then you need to specify what should happen after the previous expression is TRUE, here we specify the ProductID field, but you can specify anything you want; you can also leave TRUE or FALSE, up to you
ELSE NULL END – at the end, you need to specify the result that will be displayed if the condition is FALSE and, as before, close the expression using END
On the screen, you can see that now we have only those ProductIDs in which the numbers 7 are found.
IF STARTSWITH(STR([ProductID]), '7') THEN ProductID ELSE NULL END
IF STARTSWITH(STR([ProductID]), ‘7’) – this is the same as in the previous expression; only the search in the string is performed only by the first character.
Read How do the INCLUDE function and FIXED function (LOD expression) work while adding a condition for both functions? What is the difference between the INCLUDE function and the FIXED function?