Visualize answers to multiple choice questions in Tableau using ELSEIF statements

Question

I would like to make a calculated field in Tableau where I am able to visualize the amount of occurences of a string visualized.

I have made the following calculated field in Tableau.

IF [Avada Cadabra] = 1  THEN   'Avada Cadabra'  
ELSEIF [Aloha amora] = 1  THEN   'Aloha amora'  
ELSEIF [Depulso Levioso] = 1  THEN   'Depulso Levioso'  
ELSEIF [Levioso] = 1  THEN   'Levioso' 
ELSEIF [Protego] = 1  THEN   'Protego' 
ELSEIF [Avada Cadabra] = 1  THEN   'Avada Cadabra'    

END

However, as multiple options can be provided in the column where the data is stored, Tableau only takes the first option it encounters and sums this to the total amount of times it encountered this option, due to ELSEIF statement.

Would there also be another option to count the total amount of times that a certain string is counted in the dataframe, even if multiple string are encountered for a particular row?

Please see the following link for the workbook:

https://public.tableau.com/app/profile/tim.schouten/viz/Book1_16796557860290/Sheet1?publish=yes

I hope that my question is clear enough. If not, please let me know.

Thanks in advance.

Answer 1

How is your data structured? In your calculated field, it seems that you have 5 different columns, with one line being repeated. I'm assuming those columns are populated by either 1s or 0s, and if it's 1 then you want the value of the calculated field to be the name of the column. However, in your question, you say multiple options can be provided in the column where the data is stored. If there is one column that I assume is composed of strings, which contain the names of multiple spells for every row, what you can do is create a similar list of elseif statements, starting with checking to see if the row contains every spell and working your way down. For example,

IF CONTAINS([SPELLS], 'Avada Cadabra') 
...
AND CONTAINS([SPELLS], 'Aloha amora')
THEN 'Avada Cadabra, Aloha amora, Depulso Levioso, Levioso, Protego'
ELSEIF ...

** This will be a really long field.

From there you can do a count distinct of the different values and you're set. It really depends on how your data is structured. If you've already pivoted the data based off of the name of the spell, then your job is that much easier, but you have to be careful calculating any other metrics because of the row duplication. I hope this was helpful for you, if you've got any other questions lemme know.