How to Select multiple related columns in add calculated fields in Quicksight parameter using ifelse?

Question

I have a parameter 'type' in a table and it can have multiple values as follows -

1. human
2. chimpanzee
3. orangutan

I have 3 columns related to each type in the table -

1. human_avg_height, human_avg_weight, human_avg_lifespan
2. chimpanzee_avg_height, chimpanzee_avg_weight, chimpanzee_avg_lifespan
3. orangutan_avg_height, orangutan_avg_weight, orangutan_avg_lifespan

So if i select the type as human, the quicksight dashboard should only display the three columns -

1. human_avg_height, human_avg_weight, human_avg_lifespan

and should not display the following columns -

1. chimpanzee_avg_height, chimpanzee_avg_weight, chimpanzee_avg_lifespan
2. orangutan_avg_height, orangutan_avg_weight, orangutan_avg_lifespan

I created the parameter type and in the add calculated fields I am trying to use ifelse to select the columns based on the parameter selected as follows -

ifelse(${type}='human',{human_avg_height}, {human_avg_weight}, {human_avg_lifespan},{function})

I also tried -

ifelse(${type}='human',{{human_avg_height}, {human_avg_weight}, {human_avg_lifespan},{function}})

And -

ifelse(${type}='human',{human_avg_height, human_avg_weight, human_avg_lifespan},{function}})

But none of it is working. What am i doing wrong ?

Answer 1

One way to do this would be to use three different calculated fields, one for all the heights, one for weights and one for lifespan. The heights one would look like this:

ifelse(
    ${type}='human',{human_avg_height}, ifelse(
    ${type}='chimpanzee', { chimpanzee_avg_height},  ifelse(
    ${type}='orangutan',{ orangutan_avg_height},  
    NULL
    )))

Make another calculated field for weights and lifespan and then add these calculated fields to your table, and filter by type.

To make it clear to the viewer what data is present, edit the Title of the visual to include the type:

${type} Data

Answer 2

You have to create one calculated field for each measure using the ifelse with the type to choose the correct vale, but is not necessary to create inner ifelse as skabo did, the if else syntax is ifelse(if, then [, if, then ...], else) so you can define the calculated fields as follows:

avg_height = ifelse(${type}='human', {human_avg_height}, ${type}='chimpanzee', {chimpanzee_avg_height},${type}='orangutan', {orangutan_avg_height}, NULL)

avg_weight = ifelse(${type}='human', {human_avg_weight}, ${type}='chimpanzee', {chimpanzee_avg_weight},${type}='orangutan', {orangutan_avg_weight}, NULL)

avg_lifespan = ifelse(${type}='human', {human_avg_lifespan}, ${type}='chimpanzee', {chimpanzee_avg_lifespan},${type}='orangutan', {orangutan_avg_lifespan}, NULL)

Then use those calculated fields in your visuals.