# Powerpivot filter - Does not contain



## kshen (Feb 21, 2017)

Hi, I'm in Powerpivot trying to place a filter in the design wizard for a dimension which is "does not contain" a string of 4 letters "rlsa"(vs operator = contains)

Is there a way I can easily do this? I've been searching the MDX operator commands and I can't find anything 

thanks!


----------



## FranzV (Feb 21, 2017)

You could either create a calculated column if you need to use slicers or report filters or a measure and filter your data against that measure.

For the calculated column you can use ISERROR and SEARCH just like you would in Excel and you can the filter all TRUE rows.

```
rlsaNotFoundColumn =
ISERROR ( SEARCH ( "rlsa", YourTable[YourColumn] ) )
```

For the measure you can use SUMX to count all rows where "rlsa" cannot be found.  You can use this measure to filter your pivot at any level.

```
rlsaNotFoundMeasure :=
1
    * (
        SUMX ( TableCC, IF ( ISERROR ( SEARCH ( "rlsa", TableCC[Column1] ) ), 1 ) ) > 0
    )
```

I hope it helps.


----------



## kshen (Feb 22, 2017)

Sorry I should have clarified. I'm trying to do this in the filter section itself, not using calculated column because if I pull this field into my table it 100x my number of rows.


----------



## FranzV (Feb 22, 2017)

kshen said:


> Sorry I should have clarified. I'm trying to do this in the filter section itself, not using calculated column because if I pull this field into my table it 100x my number of rows.



You can drag the calculated column into the Report Filter area without adding any rows to the table.


----------



## kshen (Feb 22, 2017)

Thanks! didn't know about the calculated members function
I can't use the search function. It's saying its not available in sql server 2014.
I tried using instr and a couple others but it wasn't able to convert the dimension into a string


----------



## FranzV (Feb 22, 2017)

That is weird since it is not a new function.  The msdn reference page for SEARCH has articles for 2008 R2 and 2012 as well.

The problems seems to be beyond my knowledge of DAX, but maybe some screenshots with sample data and the error messages could help someone else to find a solution for you.


----------



## kshen (Feb 22, 2017)

[/URL][/IMG]





[/URL][/IMG]


----------



## FranzV (Feb 22, 2017)

I now understand that you are not using PowerPivot (that uses DAX) and you were asking for help with MDX.  I am clueless when it comes to MDX, but this could be easily done in PowerPivot using DAX.


----------

