Mixing CONTAINSSTRING & IN keywords (dynamic number of values)

[Anonymous]

Hey everyone,

I've been trying to come up with a way to make CONTAINSSTRING & IN work together.

Here is some context :

I have an SSRS text field where the user can enter multiple values with a ',' as delimiter.

The text or number of values typed in is totally up to the user and I can't use a dropdown list as there is too much value to display.

I would like to use that input as FILTER for my DAX query, something like that :

FILTER(
VALUES('Country'[_Country]),
CONTAINSSTRING('Country'[_Country], IN {"Fi", "Ja")
)

I would then get every result with Country being either Fiji, Finland, Jamaica or Japan.

The problem that I face is obviously that CONTAINSSTRING expects 2 arguments, and I'm pretty new to DAX hence I don't know how to "format" the second argument to handle multiple values.

I've seen a lot of answers using multiple CONTAINSSTRING statements with || operators but this cannot work since the input I get is dynamic.

Any idea ?

Thank you for your help 

[v-zhangti]

Hi, @Anonymous 

You can try the following methods.

Measure = FILTER(
VALUES('Country'[Country]),
CONTAINSSTRING('Country'[Country], "Fi*Ja*Ch")
)

You retrieve one, two, or three values, and you can concatenate strings with "*". 

• You can use ? and * wildcard characters. Use ~ to escape wildcard characters.

CONTAINSSTRING function (DAX) - DAX | Microsoft Docs

Best Regards,

Community Support Team _Charlotte

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

[Anonymous]

Hey,

Thank you for your help & sorry for the delayed answer.

I tried your solution but I don't think this is working. Because it seems to be searching for a word that starts with 'Fi', has 'Ja' in the middle of it & end with 'Ch' I think ?

What I would need is something that consider evey single string independently.

An equivalent in SQL would be :

  WHERE Country LIKE '%Fi%' OR Country LIKE '%Ja%'

I'm wondering if it's even possible at this point.

[Anonymous]

Sorry, maybe I didn't explained it clearly enough. 

So this solution would work but those strings were just examples.

In a real situation, the user will type the string values in a SSRS textbox which means I can't use this solution as I may retrieve one, two or maybe three values.

I can't work with hardcoded value basically 😔

[amitchandak]

@Anonymous , Try like

FIlter('Country', CONTAINSSTRING('Country'[_Country],"Fi") || CONTAINSSTRING('Country'[_Country],"Ja") )