How to filter with DAX string that contains x

[Niels_T]

Hello,

How do you filter out string that contains specific words?

For example:

/de/catalogsearch/.......

So everything that contains /de/catalogsearch/ will be filtered out.

[PC2790 · Accepted solution]

Hi @Niels_T ,

You can do it in Power Query by creating a custom column and using the following code:

if Text.Contains([ColumnName], "catalogsearch") then "true" else "false"

You can then filter out the true values to get the desired result.

If you want to do it using DAX function, the corresponding code will look like:

ColumnName = IF(
	ISERROR(
		SEARCH("catalogsearch", TableName[ColumnName])
	),
	"true",
	"false"
)

I hope this suffice your requirement

[PaulDBrown · Accepted solution]

@Niels_T 

There are a number of ways of doing this. In my example I have a table with dates, values and a string. The objective is to exclude rows in which the string contains "bc".

If you want to use the filter in the filter pane, you can use something along the lines of:

Filter out strings containing "bc" = 
VAR Vdates  = VALUES(FactTable[date])
VAR WithBC  = CALCULATETABLE(VALUES(FactTable[date]), 
                FILTER(FactTable, CONTAINSSTRING(FactTable[String], "bc")))
RETURN
COUNTROWS(EXCEPT(Vdates, WithBC))

And the use this in the filters for the visual in the filter pane setting the desired outcome to 1:

 

or you can use the measure to filter values using CALCULATE:

Sum Does not contain "bc" = 
CALCULATE([Sum Values], 
FILTER(FactTable, [Filter out strings containing "bc"] = 1))

[PC2790 · Accepted solution]

Hi @Niels_T ,

You can do it in Power Query by creating a custom column and using the following code:

if Text.Contains([ColumnName], "catalogsearch") then "true" else "false"

You can then filter out the true values to get the desired result.

If you want to do it using DAX function, the corresponding code will look like:

ColumnName = IF(
	ISERROR(
		SEARCH("catalogsearch", TableName[ColumnName])
	),
	"true",
	"false"
)

I hope this suffice your requirement