Amazon QuickSight – notIn
The ‘notIn’ function in Amazon QuickSight checks whether a given expression is not in a specified list of literals.
Syntax
#Start# notIn(expression, [literal-1, ...]) #End#
This function takes two arguments:
- expression (required): the expression to check.
- literal-1, … (required): one or more literals to compare against the expression.
Suppose you have a dataset containing a list of products with their names, categories, and prices, and you want to create a filter that excludes products in a specific category.
Assume the following dataset:
| Product Name | Category | Price |
| Product A | Category 1 | $10 |
| Product B | Category 2 | $20 |
| Product C | Category 1 | $15 |
| Product D | Category 3 | $25 |
| Product E | Category 2 | $30 |
To exclude products in Category 1, you can create a filter using the notIn function:
- Select the visual that you want to filter.
- In the filter pane on the right-hand side, click on the “Add filter” button.
- In the “Field list” section, select the “Category” field.
- In the “Filter expression” section, select “Custom expression” from the dropdown.
- Enter the following expression:
Example
#Start# notIn({Category}, 'Category 1') #End#
This will create a filter that excludes any products with the Category value of “Category 1”.
- Click “Apply” to apply the filter to the visual.
After applying this filter, only products with Category values other than “Category 1” will be displayed in the visual. In this example, Products B, D, and E will be displayed, and Products A and C will be excluded.