# How to Filter Rows by a Value Inside a Comma-Separated Field (`contains()` Workaround Using `locate()`)
Spent a while troubleshooting this and wanted to share the solution. I’ve seen several threads with the same issue but no complete write-up.
-–
## THE PROBLEM
Common data pattern: you have a text field that stores multiple comma-separated values. Product tags, assigned regions, skill sets — anything where one row can belong to multiple categories.
You want to let dashboard users pick a value from a dropdown and filter to only rows where that comma-separated field includes the selected value.
Natural approach — use `contains()`:
```
ifelse(contains({AssignedRegions}, ${SelectedRegion}), 1, 0)
```
This throws:
> Expression {{argumentName}} for function {{functionName}} has incorrect argument type {{incorrectArgumentType}}.
**Why it fails:** QuickSight’s `contains()` only accepts a string literal as the second argument — not a column reference or parameter. Even wrapping in `toString()` doesn’t fix it. Known limitation.
And even if it did work, `contains()` would give you false positive partial matches. Searching for “US” inside “AUS,EURO” returns true because “US” is a substring of “AUS”.
Two problems. One workaround.
-–
## THE SOLUTION — `locate()` WITH COMMA-WRAPPING
`locate()` accepts both column references and parameters as arguments. The comma-wrapping technique prevents partial matches.
```
ifelse(
locate(
concat(',', replace(coalesce({AssignedRegions}, ''), ' ', ''), ','),
concat(',', replace(coalesce(${SelectedRegion}, ''), ' ', ''), ',')
) > 0,
1,
0
)
```
**What each piece does:**
| Function | Purpose |
|—|—|
| `coalesce({field}, ‘’)` | Null-safe — treats null as empty string |
| `replace(…, ’ ', ‘’)` | Strips spaces so “US, AUS” becomes “US,AUS” for clean matching |
| `concat(‘,’, …, ‘,’)` | Wraps in commas so we search for “,US,” instead of just “US” |
| `locate(haystack, needle)` | Returns position if found (> 0), or 0 if not found |
**Why the comma-wrapping matters:**
Without it, searching for “US” in “AUS,EURO” returns a match because “US” appears inside “AUS”. With comma-wrapping, we search for `,US,` inside `,AUS,EURO,` — no match. But `,US,` inside `,US,AUS,EURO,` — match at position 1. Exact token matching.
-–
## EXAMPLE — Sales Rep Regional Assignment Filter
**Scenario:** You manage a sales team. Each rep is assigned to multiple regions stored in a comma-separated field. Managers want to select a region from a dropdown and see only the reps who cover that region.
**Sample data:**
| RepID | RepName | AssignedRegions | Q1Sales |
|—|—|—|—|
| 1 | Alice Chen | US, EMEA, APAC | 245000 |
| 2 | Bob Martinez | US, LATAM | 189000 |
| 3 | Carol Wu | APAC, EMEA | 312000 |
| 4 | David Kim | LATAM | 97000 |
| 5 | Eva Singh | US, APAC | 278000 |
### Step 1 — Create a Parameter
- **Name:** `SelectedRegion`
- **Type:** String, Single value
- **Default value:** `ALL`
### Step 2 — Create a Control
- Link a dropdown control to the `SelectedRegion` parameter
- Specific values: ALL, US, EMEA, APAC, LATAM
### Step 3 — Create the Calculated Field
Name it `ShowRep`:
```
ifelse(
${SelectedRegion} = ‘ALL’,
1,
ifelse(
locate(
concat(',', replace(coalesce({AssignedRegions}, ''), ' ', ''), ','),
concat(',', replace(coalesce(${SelectedRegion}, ''), ' ', ''), ',')
) > 0,
1,
0
)
)
```
The outer `ifelse` handles the “ALL” case — when the user wants to see every rep without filtering.
### Step 4 — Add a Filter to Your Visual
- **Field:** `ShowRep`
- **Condition:** Equals `1`
**Results:**
| Selection | Reps Shown |
|—|—|
| ALL | Alice, Bob, Carol, David, Eva (all reps) |
| US | Alice, Bob, Eva |
| EMEA | Alice, Carol |
| APAC | Alice, Carol, Eva |
| LATAM | Bob, David |
-–
## COPY-PASTE TEMPLATE
For any comma-separated field with a single-value parameter and an ALL option:
```
ifelse(
${YourParameter} = ‘ALL’,
1,
ifelse(
locate(
concat(',', replace(coalesce({YourCommaField}, ''), ' ', ''), ','),
concat(',', replace(coalesce(${YourParameter}, ''), ' ', ''), ',')
) > 0,
1,
0
)
)
```
Replace `{YourCommaField}` with your field name and `${YourParameter}` with your parameter name. Drop the ALL check if you don’t need it.
-–
## ADVANCED PATTERN — COMPOSITE SEARCH TOKEN (PARAMETER + FIELD)
The basic pattern searches for a single parameter value inside a comma-separated list. But what if the tokens in your list are compound values — two dimensions joined together — and the search key needs to be built dynamically from a parameter AND a field on the same row?
This comes up when your comma-separated list contains things like “REGION-SEGMENT” or “INSURER-PLANTYPE”, and you need to match on one half from a user selection and the other half from the row’s own data.
**The formula shape changes to:**
```
ifelse(
locate(
concat(',', replace(coalesce({CompoundList}, ''), ' ', ''), ','),
concat(',', replace(coalesce(concat(${SelectedParam}, '-', {RowField}), ''), ' ', ''), ',')
) > 0,
1,
0
)
```
**Key difference:** instead of searching for just `${SelectedParam}`, we search for `concat(${SelectedParam}, ‘-’, {RowField})` — building the needle dynamically from a parameter + a field.
Three real-world scenarios where this applies.
-–
### Scenario 1: Healthcare Provider Network — “Does my doctor accept my plan?”
Insurance dashboard. Each provider has a comma-separated list of accepted plan codes. Each code is a compound of insurer + plan type (e.g., “BLUECROSS-PPO”).
**Sample data:**
| ProviderID | ProviderName | AcceptedPlans | PlanType | Specialty |
|—|—|—|—|—|
| 1 | Dr. Sarah Lee | BLUECROSS-PPO,AETNA-HMO,UNITED-PPO | PPO | Cardiology |
| 2 | Dr. James Park | AETNA-HMO,AETNA-PPO | HMO | Orthopedics |
| 3 | Dr. Maria Santos | BLUECROSS-PPO,BLUECROSS-HMO,UNITED-HMO | PPO | Pediatrics |
| 4 | Dr. Kevin Brown | UNITED-PPO,UNITED-HMO | PPO | Dermatology |
| 5 | Dr. Amy Chen | AETNA-PPO,BLUECROSS-HMO | PPO | Neurology |
**Parameter:** `SelectedInsurer` — values: ALL, BLUECROSS, AETNA, UNITED
**Calculated field `ShowProvider`:**
```
ifelse(
${SelectedInsurer} = ‘ALL’,
1,
ifelse(
locate(
concat(',', replace(coalesce({AcceptedPlans}, ''), ' ', ''), ','),
concat(',', replace(coalesce(
concat(${SelectedInsurer}, '-', {PlanType}),
''), ' ', ''), ',')
) > 0,
1,
0
)
)
```
**Results when selecting “BLUECROSS”:**
| Provider | PlanType | Searches For | Match? | Why |
|—|—|—|—|—|
| Dr. Sarah Lee | PPO | `,BLUECROSS-PPO,` | 
| Found in AcceptedPlans |
| Dr. James Park | HMO | `,BLUECROSS-HMO,` | 
| Only AETNA plans |
| Dr. Maria Santos | PPO | `,BLUECROSS-PPO,` | | Found in AcceptedPlans |
| Dr. Kevin Brown | PPO | `,BLUECROSS-PPO,` | | Only UNITED plans |
| Dr. Amy Chen | PPO | `,BLUECROSS-PPO,` | | Has BLUECROSS-HMO, not PPO |
The filter correctly distinguishes between BLUECROSS-PPO and BLUECROSS-HMO even though both start with “BLUECROSS”.
-–
### Scenario 2: Retail Promotion Eligibility — “Which products qualify for this campaign in their category?”
Each product has a list of promo campaigns it qualifies for, stored as compound tokens like “SUMMER2025-ELECTRONICS”. A category manager selects a campaign, and the filter builds the search from the campaign + the product’s own category.
**Sample data:**
| ProductID | ProductName | PromoCampaigns | Category | Price |
|—|—|—|—|—|
| 1 | 4K Smart TV | SUMMER2025-ELECTRONICS,BOGO-ELECTRONICS | ELECTRONICS | 599 |
| 2 | Running Shoes | SUMMER2025-APPAREL,CLEARANCE-APPAREL | APPAREL | 89 |
| 3 | Bluetooth Speaker | BOGO-ELECTRONICS,CLEARANCE-ELECTRONICS | ELECTRONICS | 45 |
| 4 | Winter Jacket | CLEARANCE-APPAREL | APPAREL | 199 |
| 5 | Laptop Stand | SUMMER2025-ELECTRONICS,SUMMER2025-OFFICE | ELECTRONICS | 35 |
**Parameter:** `SelectedCampaign` — values: ALL, SUMMER2025, BOGO, CLEARANCE
**Calculated field `ShowProduct`:**
```
ifelse(
${SelectedCampaign} = ‘ALL’,
1,
ifelse(
locate(
concat(',', replace(coalesce({PromoCampaigns}, ''), ' ', ''), ','),
concat(',', replace(coalesce(
concat(${SelectedCampaign}, '-', {Category}),
''), ' ', ''), ',')
) > 0,
1,
0
)
)
```
**Results when selecting “SUMMER2025”:**
| Product | Category | Searches For | Match? |
|—|—|—|—|
| 4K Smart TV | ELECTRONICS | `,SUMMER2025-ELECTRONICS,` | |
| Running Shoes | APPAREL | `,SUMMER2025-APPAREL,` | |
| Bluetooth Speaker | ELECTRONICS | `,SUMMER2025-ELECTRONICS,` | |
| Winter Jacket | APPAREL | `,SUMMER2025-APPAREL,` | |
| Laptop Stand | ELECTRONICS | `,SUMMER2025-ELECTRONICS,` | |
Each product is checked against the campaign for its own category, not just whether the campaign name appears anywhere in the string.
-–
### Scenario 3: Sales Territory Overlap — “Which reps cover this region for each customer’s segment?”
Sales reps have territory assignments stored as compound tokens: region + customer segment (e.g., “WEST-ENT” for West region, Enterprise segment). A VP selects a region, and the filter matches against each customer row’s own segment.
**Sample data:**
| RepID | RepName | Territories | CustomerSegment | Q1Pipeline |
|—|—|—|—|—|
| 1 | Alice Chen | WEST-ENT,EAST-ENT,CENTRAL-SMB | ENT | 1200000 |
| 2 | Bob Martinez | WEST-SMB,WEST-MID | SMB | 450000 |
| 3 | Carol Wu | EAST-ENT,EAST-SMB | ENT | 890000 |
| 4 | David Kim | CENTRAL-ENT,CENTRAL-MID | MID | 670000 |
| 5 | Eva Singh | WEST-ENT,CENTRAL-ENT | ENT | 1050000 |
**Parameter:** `SelectedRegion` — values: ALL, WEST, EAST, CENTRAL
**Calculated field `ShowRep`:**
```
ifelse(
${SelectedRegion} = ‘ALL’,
1,
ifelse(
locate(
concat(',', replace(coalesce({Territories}, ''), ' ', ''), ','),
concat(',', replace(coalesce(
concat(${SelectedRegion}, '-', {CustomerSegment}),
''), ' ', ''), ',')
) > 0,
1,
0
)
)
```
**Results when selecting “WEST”:**
| Rep | CustomerSegment | Searches For | Match? | Why |
|—|—|—|—|—|
| Alice Chen | ENT | `,WEST-ENT,` | | Found in Territories |
| Bob Martinez | SMB | `,WEST-SMB,` | | Found in Territories |
| Carol Wu | ENT | `,WEST-ENT,` | | Only EAST territories |
| David Kim | MID | `,WEST-MID,` | | Only CENTRAL territories |
| Eva Singh | ENT | `,WEST-ENT,` | | Found in Territories |
The filter finds reps who cover the selected region for the specific customer segment on that row — not just any rep who has “WEST” anywhere in their territory list.
-–
## COMPOSITE TOKEN TEMPLATE
```
ifelse(
${YourParameter} = ‘ALL’,
1,
ifelse(
locate(
concat(',', replace(coalesce({YourCommaField}, ''), ' ', ''), ','),
concat(',', replace(coalesce(
concat(${YourParameter}, '-', {YourRowField}),
''), ' ', ''), ',')
) > 0,
1,
0
)
)
```
Replace `{YourCommaField}` with the comma-separated list field, `${YourParameter}` with your parameter, and `{YourRowField}` with the row-level field that forms the second half of the compound token. Adjust the delimiter (`-`) to match whatever separator your data uses.
-–
## WHY NOT JUST USE `contains()`?
| Issue | `contains()` | `locate()` + comma-wrapping |
|—|—|—|
| Accepts parameters | String literals only | Parameters and field references |
| Exact token matching | Partial substring matches | Comma-wrapping prevents false positives |
| Null safety | | Via `coalesce()` |
| Dynamic composite keys | | Build needle with `concat()` |
-–
Hope this saves someone the hours I spent on it. Happy to answer questions.
**Related threads:**
- [contains() function using two columns]( Calculated field - "Contains" function using two columns )
- [contains() + parameter incorrect argument type]( Help with contains() + parameter in calculated field — “incorrect argument type” error )
- [Filter by a string separated by commas](Filter by a String separated by commas / "Contains" function argument type)