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

Question & Answer
6

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

16 Likes