Find upper case string in multiple columns

[jrmaier]

I learned how to use this and it works for one column. 

Uppercase =

IF(

    EXACT(UPPER('132330'[FirstName]), '132330'[FirstName])

        , "yes", "no")

Is it possible to edit it so it searches multiple columns? Would it be better to create a results column for each field (first & last name, address, email address) I am checking for all caps? Table is not very big. 10K+ rows.

Also wondering if a record is empty there is something I can add so the uppercase result does not come back as a "yes". See image.

[jrmaier]

@amitchandak I am using this for a single column. When filtering a slicer to show "yes" results it is returning numbers as all uppercase values. Is there a way to remove them?

[v-zhangti · Accepted solution]

Hi, @jrmaier 

Column:

Uppercase =
IF (
    [FirstName] = BLANK ()
        && [LastName] = BLANK (),
    BLANK (),
    IF (
        EXACT ( UPPER ( '132330'[FirstName] ), '132330'[FirstName] )
            && EXACT ( UPPER ( '132330'[LastName] ), '132330'[LastName] ),
        "yes",
        "no"
    )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jrmaier]

@Community Support Team _Charlotte I was looking to get a "yes" result if the value in any of the columns was all upper case even if the other columns in the row weren't. 

[jrmaier]

@v-zhangti thank you very much for the method to remove the false result when the Prefix field is empty.
That worked!

[jrmaier]

@v-zhangti thank you very much for replying. That looks great and I can use in other applications, but I am wanting to have the result to return "yes" if any of the values in a row are all upper case. In your example, all rows would return "yes". That way I will be able to find constituents who have any fields all upper.

[amitchandak]

@jrmaier , You have add a condition with add && , OR ||

example

IF(
EXACT(UPPER('132330'[FirstName]), '132330'[FirstName]) || EXACT(UPPER('132330'[LatsName]), '132330'[LastName])
, "yes", "no")

with and

IF(
EXACT(UPPER('132330'[FirstName]), '132330'[FirstName]) && EXACT(UPPER('132330'[LatsName]), '132330'[LastName])
, "yes", "no")

[jrmaier]

@amitchandak can this be combined with some of this code below at the beginning to not include empty cells giving a "yes" anwser?

Uppercase =
IF (
[FirstName] = BLANK ()
&& [LastName] = BLANK (),
BLANK (),
IF (
EXACT ( UPPER ( '132330'[Prefix] ), '132330'[Prefix] )
&& EXACT ( UPPER ( '132330'[FirstName] ), '132330'[FirstName] ),
"yes",
"no"
)

[amitchandak]

@jrmaier , Try like

Uppercase =
Switch(True(),
[FirstName] = BLANK ()
&& [LastName] = BLANK (),
EXACT ( UPPER ( '132330'[Prefix] ), '132330'[Prefix] )
&& EXACT ( UPPER ( '132330'[FirstName] ), '132330'[FirstName] ),
"yes",
"no"
)

[jrmaier]

@amitchandak I get this error message:

Function 'SWITCH' does not support comparing values of type True/False with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

[jrmaier]

@amitchandak thank you very much for replying. It is an honor to have you help me. I have admired your knowledge and willingness to help others for a long time. That looks great and I can use it in other applications, but I am wanting to have the result to return "yes" if any of the values in a row are all upper case. That way I will be able to find constituents who have any fields that are all upper case.

[amitchandak]

@jrmaier , As of now, I think you need to have the same condition on all columns using or (||)

for and we can even concat and check

IF(
EXACT(UPPER('132330'[FirstName] & '132330'[LatsName]) , '132330'[FirstName] & '132330'[LastName])
, "yes", "no")

[jrmaier]

@amitchandak I tried this, but get a "no" when the "IQ" last name should give a "yes" result.

Final =

IF (

    [FirstName] = BLANK ()

        && [LastName] = BLANK (),

    BLANK (),

    IF(

EXACT(UPPER('132330'[Prefix] &'132330'[FirstName] &'132330'[LastName] &'132330'[Suffix] &'132330'[PreferredName] &'132330'[HomeAddress1] &'132330'[HomeAddress2] &'132330'[HomeCity] & '132330'[Email]) , '132330'[Prefix] &'132330'[FirstName] &'132330'[LastName] &'132330'[Suffix] &'132330'[PreferredName] &'132330'[HomeAddress1] &'132330'[HomeAddress2] &'132330'[HomeCity] & '132330'[Email])

, "yes", "no"))

[v-zhangti]

Hi, @jrmaier 

You can try the following methods.

Uppercase = 
IF (
    [FirstName] = BLANK ()
        && [LastName] = BLANK (),
    BLANK (),
    IF (
        EXACT ( UPPER ( '132330'[FirstName] ), '132330'[FirstName] )
            || EXACT ( UPPER ( '132330'[LastName] ), '132330'[LastName] )
            ||EXACT ( UPPER ( '132330'[Name] ), '132330'[Name]),
        "yes",
        "no"
    )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.