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).

9 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!

Hi @darcoli , thank you so much for providing the workaround for the multi-select capability. It shouldn’t be that hard, we are prioritizing this feature in our roadmap!
@tjardine, thanks for the request!

@darcoli Wow, super clever solution! I dont think I would have figured that one out… :slight_smile: Great work

wow, this is super! we definitely should provide a feature in future to support it!

:slight_smile: glad others are finding this workaround useful for the time being.

Going forward, though, I agree that there could be a simpler way to do this. I think the most obvious approach would be if Navigation Actions supported adding and removing values into multivalued parameters (afaik currently these only support setting single valued parameters).

thank you mate! genius solution!

wonderful solution. Can i try this for pivot table as in my case I just want to see selected values and exclude rest for a period of 6 weeks in a pivot table, can this be doable?

If I m not mistaken, back then the only requirement for this not being a Pivot Table was because it was not allowed to Hide fields on Pivot Tables (which is one of the steps in the solution above). However, since then, QuickSight rolled out functionality to allow hiding fields on Pivot Tables as well… so it should be possible with Pivot Tables :slight_smile:

@tjardine @darcoli when implementing this to include selections in the filter, I’ve created a solution to show all values when none are selected:

  1. Create calculated field SingleStringforVisualizationFilter:
    “string”

  2. Create calculated field to return total value for Selected_facility metric
    SelectAllVisualization:
    sumOver({Selected_facility},[SingleStringforVisualizationFilter],PRE_AGG)

  3. Update target visualization filters to return all facilities when none are selecter: