How to create a list of of strings to store and compare via calculated fields.

Hey all,

I am attempting to store multiple lists of values in Tableau, they can be thought about as a reference table. To be more elaborate, I have around 15 list of 30+ values a piece that need to be compared against fields that were imported with the dataset. I have tried multiple different approaches to this, and I have found one that works every time, it just gets a bit wordy. My objective with majority of functions, using the list, is to return zero if the field is null, then return one if any of the values located in the list equal a value in the field and zero if none are located.

Option 1 (the one I'm currently using, that can get wordy):

if isnull([field1])

then 0

elseif [field1] = "value1" or

[field1] = "value2" or

[field1]= "value3" or.....

...then 1

else 0

end

This is done for all values located in the list.

Option 2 (What I thought would work):

This option would be the most ideal if possible. The goal with this attempt is to store all the string values into a calculated field so that I can call that as a dimension string list. I did the following:

"value1" +

"value2"+

"value3"......

This was done for all values in the list. After doing this the values returned in a singular row next to each other, so I attempted again but with a delimiter:

"value1" + ", " +

"value2"+ ", " +

"value3" + ", "+......

This worked in the aspect of producing string values. But once again they were in the same row, so I couldn't do my goal of the following:

if isnull([field1])

then 0

else if [field1] = [Calculated field of list values]

then 1

else 0

end

Which I believe can only be done if they are located in separate rows / individual records.

Option 3 (using parameters):

This is the most common solution I found online. But since I am selecting multiple values, the single valued parameter list wasn't the most ideal fit. There were solutions that said I could add an All selection, but when I tried that it returned all values in the dataset rather than returning only the values associated with the list values in the parameter.

If anyone could give insight on how I could use option 2 or 3 that would be beneficial. Like previously said, option 1 works perfectly fine. It just gets wordy because for some instances I create nested if statements that have 2/3 lists and it can be over 100 rows. Or if I use the same list in 5 different calculated fields, the process would be a lot smoother and efficient if I could call a calculated field rather than saying the 30+ values every time. Thanks for the help!