Search for a keyword in a Comma separated list of values in a column

[GAURAVG]

Hi,

I have a table called "Details" as below. (Note:Actual data table has 3000 entries)

PRODUCT	REGION
A	USA,Australia,Canada
B	Australia
C	India,China
D	USA,Canada,Australia
E	India,USA
F	USA

I want to have a manual column, where if the value "Australia" or "USA" is found, it prints "Found".

So basically, it should print "Found" for A,B,D,E,F

I tried doing this with code :

Details[REGION] in {"USA", "Australia"}

But this somehow does not work , only if an exact match is found, it prints "Found"

It gives "Found" only for B,F

[v-alq-msft · Accepted solution]

Hi, @GAURAVG 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a calculated column or a measure as below.

Calculated column:

Column = 
IF(
    CONTAINSSTRINGEXACT([REGION],"Australia")||
    CONTAINSSTRINGEXACT([REGION],"USA"),
    "Found"
)

Measure:

Measure = 
IF(
    CONTAINSSTRINGEXACT(MAX('Table'[REGION]),"Australia")||
    CONTAINSSTRINGEXACT(MAX('Table'[REGION]),"USA"),
    "Found"
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GAURAVG]

Thanks a lot. This works perfectly

[amitchandak]

@GAURAVG , one why is split data into row and then you can use in; in filter https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

calculate(countrows(Table), filter(Table, table[Split region] in {"USA", "Australia"})

with current format

calculate(countrows(Table), filter(Table, search("USA",table[Split region],,0)>0 ||  search("Australia",table[Split region],,0)>0 ))

 table[Split region] in {"USA", "Australia"})

[GAURAVG]

thanks for the hint, but:

1. I do not want the count, i need it to print some text, if it satisfies the condition,like "Found"

2. I cannot split the column manually, as in my original data, i have more than 20 comma separated values for each Region row.