Filtering a field in Set Analysis with '' in the string value

[50ShadesOfSalty]

Hello everyone,

I'm currently having an issue filtering a value that has '' in the string.

For example, consider the following example:

ID | NAME

1 | Mike

2 | Paul

3 | Robinson

4 | Owen 'The Flash'

5 | John 'The Cool'

When i try to: Sum( {< [NAME]={'Mike', 'Paul'} >}[Sales]), all works as it should.

Now, if i try to do Sum( {< [NAME]={'Owen 'The Flash''} >}[Sales]), i encounter an error because of the ' that are inside of the string value that i want to filter.

Also, here are some examples that i tried in order to overcome this issue:

'Owen '&chr(39)&'The Flash'&chr(39)

$(=chr(39)&'Owen '&chr(39)&'The Flash'&chr(39)&chr(39))

Any help for this issue is highly apreciated, as long as i'm having some troubles with this issue!

Thanks,

50ShadesOfSalty

[veidlburkhard · Accepted solution]

Hi,

try

Sum( {< [NAME]={"Owen 'The Flash'"} >}[Sales])

This should work.

Regards

Burkhard

[petter]

If you still want to do a string literal match and not a string search then you can do it by escaping the character by having it twice like this:

Sum( {< [NAME]={'Owen ''The Flash'''} >}[Sales])

Notice that there are only single quotes in there - not double quotes although it might look like it...

Sum( {< [NAME]={"Owen 'The Flash'"} >}[Sales])

Will also work although this is a search and not a match - and it is case-insensitive whereas using single quotes will be case-sensitive...

[50ShadesOfSalty]

This actually worked! 

Thank you!