Powerpivot filter - Does not contain

[kshen]

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]

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]

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]

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]

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]

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]

[/URL][/IMG]

[/URL][/IMG]

 

[FranzV]

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.