# Filter Rows by a Value Inside a Comma-Separated Field with parameter

**URL:** <https://community.amazonquick.com/t/filter-rows-by-a-value-inside-a-comma-separated-field-with-parameter/51686>\
**Category:** Technical Articles\
**Tags:** amazon-quick, Business-Intelligence-Engineer, quick-sight\
**Created:** [March 3, 2026, 9:40pm UTC](https://community.amazonquick.com/t/filter-rows-by-a-value-inside-a-comma-separated-field-with-parameter/51686 "2026-03-03T21:40:43Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![Ramon\_Lopez](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/ramon_lopez/32/2738_2.png) [@Ramon\_Lopez](https://community.amazonquick.com/u/Ramon_Lopez)\
**Post date:** [March 3, 2026, 9:40pm UTC](https://community.amazonquick.com/t/filter-rows-by-a-value-inside-a-comma-separated-field-with-parameter/51686/1 "2026-03-03T21:40:44Z")

</div>

**# 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](https://community.amazonquick.com/t/calculated-field-contains-function-using-two-columns/9466) )

- [contains() + parameter incorrect argument type]( [Help with contains() + parameter in calculated field — “incorrect argument type” error](https://community.amazonquick.com/t/help-with-contains-parameter-in-calculated-field-incorrect-argument-type-error/47957) )

- [Filter by a string separated by commas]([Filter by a String separated by commas / "Contains" function argument type](https://community.amazonquick.com/t/filter-by-a-string-separated-by-commas-contains-function-argument-type/46428))