How can I select multiple lines from a table? and how could I then DESELECT those items to exclude them form the analysis?
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.
4 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:
- Add a string parameter called
selected_item_idswith a comma,as its default value. This parameter will contain a comma separated list of the selecteditem id's. - Add a calculated field named
updated_selected_item_idswith 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.
-
Add a table visual (important normal table not pivot table).
-
Add both the
item_idandupdated_selected_item_idsto the Group By field well. -
Sort the table by the
item_idfield -
Set the
updated_selected_item_idsas a hidden column in the table -
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:
- Add a calculated field called
in_selected_item_ids
ifelse(locate(${selected_item_ids}, item_id ) > 0 , 1, 0)
-
Go to conditional formatting for the table, and under “Select a column” choose “[Entire Row]”
-
Click on “Add Background Color”
-
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).
14 Likes
Thank you I will look over this!
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.
2 Likes
I’ve marked this as a feature request so the appropriate team can take a look at it. Thank you!
1 Like
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!
wow, this is super! we definitely should provide a feature in future to support it!
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
@tjardine @darcoli when implementing this to include selections in the filter, I’ve created a solution to show all values when none are selected:
-
Create calculated field SingleStringforVisualizationFilter:
“string” -
Create calculated field to return total value for Selected_facility metric
SelectAllVisualization:
sumOver({Selected_facility},[SingleStringforVisualizationFilter],PRE_AGG) -
Update target visualization filters to return all facilities when none are selecter:
1 Like