How can I select multiple lines from a table? (and how could I then DESELECT those items to exclude them form the analysis?)

How can I select multiple lines from a table? and how could I then DESELECT those items to exclude them form the analysis?
image

At the moment QuickSight does not provide an ability to select multiple rows from a table that can be used to create “exclude” filter.

Thank you. Is there a way to simply select multiple rows from a table? (not necessarily deselect)

I had implemented this using a parameter, Actions and conditional formatting. Basically you use actions to append to a parameter or remove from a parameter (using a calculated field) and then use conditional formatting for rows matching content in the parameter. Here is what the result of that looks like

It’s quite a lengthy solution to type out, but if it is still unclear for you, I can provide step by step instructions on how to achieve it.

2 Likes

Exactly what I’m looking for! If it’s not too much trouble I would appreciate the step by step. BTW can you use this to EXCLUDE the selected items?

So, if I understand correctly, the goal is to have a table at the top of your analysis which presents a number of item_id’s. item_id's can be selected (and deselected) from this table - if they are selected then you want those item_id's to be excluded from other visuals in your analysis (or dashboard).

This can be achieved as follows:

  1. Add a string parameter called selected_item_ids with a comma , as its default value. This parameter will contain a comma separated list of the selected item id's.
  2. Add a calculated field named updated_selected_item_ids with the following definition:
ifelse(
  locate(${selected_item_ids}, item_id) > 0, replace(${selected_item_ids}, concat(item_id,','), ''),
  concat(${selected_item_ids},item_id,',')
)

This calculated field will contain the new value of the $selected_item_ids parameter when an item selected/deselected.

  1. Add a table visual (important normal table not pivot table).

  2. Add both the item_id and updated_selected_item_ids to the Group By field well.

  3. Sort the table by the item_id field

  4. Set the updated_selected_item_ids as a hidden column in the table

  5. Add a new action to this table, with

    Activation: select
    Action Type: Navigation
    Target Sheet: (choose the sheet name where you added the table)

Add a parameter under the Parameters section and choose:

    Parameter: selected_item_ids
    Set parameter value: updated_selected_item_ids

Click Add and then click Save

At this point you already have the basic mechanics to store the selected item_id's as a csv list in the $selected_item_ids parameter. If you want, you can set the title of the table to <<$selected_item_ids>> and see that the list gets updated as you click on item_ids (and that item_id’s are removed if you items that are already in the list).

Next we will add the highlight to the selected item_id's:

  1. Add a calculated field called in_selected_item_ids
ifelse(locate(${selected_item_ids}, item_id ) > 0 , 1, 0)
  1. Go to conditional formatting for the table, and under “Select a column” choose “[Entire Row]”

  2. Click on “Add Background Color”

  3. Set “Format field based on” to in_selected_item_ids

  Aggregation: SUM
  Condition: Greater Than or Equal to
  Value: 1

Choose the Highlight Color and click Apply

You should now be able to select and deselect rows in this table.

To exclude the currently selected item_id's from the rest of the visuals, add a filter on the in_selected_item_ids field with condition equals to 0. (Note this filter should not be added to the table you use to select the items).

4 Likes

Thank you I will look over this!

I got this error when setting up the calculated field

@tjardine You can initialize that parameter to a comma , when creating it in step 1.

Sorry for late reply. This worked. Super helpful thank you.

Thanks for sharing this, I implemented the solution and added the following options.

  • Sort by length to keep selected values on top, add a calculated field and sort by it.
    strlen({updated_selected_item_ids})

  • On filtering avoid no data when nothing is selected returing -1 to use in filter along with 1 using OR condition.
    added strlen(${selected_item_ids})<=1,-1 to the folowing.

in_selected_item_ids = ifelse(locate(${selected_item_ids},item_id ) > 0 , 1, strlen(${selected_item_ids})<=1,-1, 0)

Note:
Logic needs to be added to make it work with null values.

I think this should be provided by the tool. :frowning_face:

1 Like

I’ve marked this as a feature request so the appropriate team can take a look at it. Thank you!