topic Mixing CONTAINSSTRING & IN keywords (dynamic number of values) in DAX Commands and Tips

Mixing CONTAINSSTRING & IN keywords (dynamic number of values)

Anonymous — Wed, 24 Aug 2022 08:31:52 GMT

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

Re: Mixing CONTAINSSTRING & IN keywords (dynamic number of values)

amitchandak — Wed, 24 Aug 2022 10:20:15 GMT

Anonymous , Try like

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

Re: Mixing CONTAINSSTRING & IN keywords (dynamic number of values)

Anonymous — Wed, 24 Aug 2022 12:46:58 GMT

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 😔

Re: Mixing CONTAINSSTRING & IN keywords (dynamic number of values)

v-zhangti — Thu, 01 Sep 2022 07:14:14 GMT

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.

Re: Mixing CONTAINSSTRING & IN keywords (dynamic number of values)

Anonymous — Tue, 06 Sep 2022 12:22:47 GMT

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.