Parametric calculated field in the control dropdown

Question & Answer
, ,
1

Hello,
I have a calculated field (called status_lang) that is parametric based on the Language :

   ifelse(${Language} = 'ENG', {status_en}, 
          ${Language} = 'ITA', {status}, 
          {status} )

I want to use that field to create a control, in which values in the dropdown change based on the Language.

In my analysis, I created a parameter called “status” and a filter linked to the field “status_lang” to reach this. But when I change the Language the categories in the dropdowns don’t change accordingly.

Any suggestion?

Thanks!

2

Hello @martina, is the dropdown that contains the values you are trying to update connected to a dataset field? If so, you can try using the “Show relevant values” based on the selection of the Language parameter so it can update for matching rows.

I have a feeling though that this won’t work in your case. I think the likely way to manage this will be to update your dataset. You will want to set it up so rows will only include values related to their language selection.

You would need a Language column which would return the list of language options, then if the option is ‘Eng’, all corresponding row values will be in english. This will work as expected when you add cascading filters for your remaining dropdowns like suggested above. I will mark this as a solution, but if you have questions about changing the format of your dataset for this, please let me know. It will likely require Unions based on the language group to be created properly.

3

Hi Dylan, thank you for your answer.

My case does not involve duplicate rows (one for each language) because it would not scale well and would involve re-indexing the rows already in the dataset each time a new language is to be enabled. Therefore, I had planned to create a column with translated values for each language so that I could use the “Language” parameter as a discriminator (as suggested by the post Build a multilingual dashboard with Amazon Athena and Amazon QuickSight | AWS Big Data Blog).

It might be useful to include this possibility as feature on QS.

4

Hello @martina, I completely understand not wanting to duplicate the number of rows based on the number of languages. It definitely is not the ideal scenario, it is just one that I know works went trying to filter the values shown in a dropdown.

The problem with the current set up is that if the English values and Italian values for a field are in 2 different columns, there is no way to dynamically change the column a control is displaying based on the value of a different parameter. I have one more alternative way that I can think of to fulfill this with the current available functionality. It is a little messy, but it would work.

You would need to utilize the Free-Form view for your sheet. If you click settings while on an analysis, you can make the change there. This allows you to add Rules to visuals that will show/hide based on a parameter value.

Then you would basically need a set of dropdowns and visuals (excluding the original that allows you to select a language) for each language option available. If you add your controls to the sheet, you are able to add Rules for them as well. The reason you would need to duplicate your visuals is because each one will filter by the parameter value being set by the dropdowns for that language.

Basically, when you would select your language, the visuals would change (pretty seamless for the user), all dropdowns will be linked to the correct language field, and only that language will show in the visuals.

This is the other alternative work-around for this. Since this is not an optimal solution, I will also mark this as a feature-request for more dynamic parameter options in controls and/or dynamic language filtering in QuickSight visuals. Thank you for your patience!

1 Like