Filters: Exclude Numbers

[Heinrich]

Hello

Is it possible to exclude numbers or only contain letters in a filter.

Regards

Heinrich

[d_rohlfs · Accepted solution]

Hi @Heinrich, 

You could create a calculated column that is 

Text Check = IF(ISERROR(VALUE('Table1'[Field 1])), "TEXT", "NUMBER")

Then place this into a a filter and you can choose to filter out rows where the entire cell content for that row is a number.

[d_rohlfs · Accepted solution]

Hi @Heinrich, 

You could create a calculated column that is 

Text Check = IF(ISERROR(VALUE('Table1'[Field 1])), "TEXT", "NUMBER")

Then place this into a a filter and you can choose to filter out rows where the entire cell content for that row is a number.

[Heinrich]

Hello d_rohlfs

Thank you very much.

With "Number" do you mean a specific number or is this a variable?
I can not tell you the number exactly.

Regards

Heinrich

[kpost]

A similar solution to @olgad but this moves the logic into a measure instead, and works for some kinds of visuals.

Create a measure like this:

visualFilter = 
IF(

CONTAINSSTRING([value], "0")
|| CONTAINSSTRING([value], "1")
|| CONTAINSSTRING([value], "2")

|| CONTAINSSTRING([value], "3")

|| CONTAINSSTRING([value], "4")

|| CONTAINSSTRING([value], "5")

|| CONTAINSSTRING([value], "6")

|| CONTAINSSTRING([value], "7")

|| CONTAINSSTRING([value], "8")

|| CONTAINSSTRING([value], "9"),

1,

0

)

Then make this measure a filter on the visual and only include records where [visualFilter] is 0

[Heinrich]

Hello kpost

Thank you but there are multiple digits so one single number is to few.

Do you have the possibility to expand this solution?
Regards
Heinrich

[kpost]

My solution filters on whether there are ANY numbers, anywhere in the field.  It is not checking whether the field is equal to 0,1,2,3,4,5,6,7,8,9.  Rather, it is checking whether those characters are found anywhere in the string.

[Heinrich]

Thank you @kpost 
It was my error. I wanted to filter out every name that starts with a number
Do you have such a solution
Regards
Heinrich

[kpost]

@olgad  nailed it.

If the filter condition is that you want to exclude names that start with a number, rather than names that contain a number then use:

if(left(Table[YourColumn], 1)
in {"1","2","3","4", "5", "6", "7", "8", "9", "0"}, 1, 0)

rather than CONTAINSSTRING. 

then use that as a filter on the visual and do a 'basic' filter on fields where it equals zero.

[Heinrich]

Hello @kpost 

Thank you very much.

Enjoy the weekend

Regards

Heinrich

[olgad · Accepted solution]

Hi @Heinrich  so, either take my first solution, there are 10 possible numbers that any number can start with and in advanced filtering say starts with instead of equals or contains. 

Or create a Calculated Column 

VizFilter =
if(left(Table[YourColumn], 1)
in {"1","2","3","4", "5", "6", "7", "8", "9", "0"}, 1, 0)

Use it as a filter.

[Heinrich]

Hi @olgad 

Thank you very much.

Have a great weekend.

Regards

Heinrich

[olgad]

Please @Heinrich dont forget to accept the solution that helped you solve the problem. Thanks, have a greate one too. 

[olgad]

A dirty solution, but nonetheless:
In the filter, you choose filter type: basic, deselct everything, pick 10 entries

Then, change the filter type to Advanced: 

Change "is" to "does not contain"  and put in 0,1,2,3,4,5 etc. and apply filter

[Heinrich]

Hello olgad

Thank you but there are multiple digits so one single number is to few.

Do you have the possibility to expand this solution?
Regards
Heinrich