topic Re: Find upper case string in multiple columns in DAX Commands and Tips

Find upper case string in multiple columns

Anonymous — Mon, 05 Dec 2022 18:27:54 GMT

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.

Re: Find upper case string in multiple columns

amitchandak — Tue, 06 Dec 2022 03:46:51 GMT

Anonymous , 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")

Re: Find upper case string in multiple columns

v-zhangti — Tue, 06 Dec 2022 07:32:59 GMT

Hi, Anonymous

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.

Re: Find upper case string in multiple columns

Anonymous — Tue, 06 Dec 2022 13:07:34 GMT

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.

Re: Find upper case string in multiple columns

Anonymous — Tue, 06 Dec 2022 13:10:15 GMT

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.

Re: Find upper case string in multiple columns

amitchandak — Tue, 06 Dec 2022 13:14:44 GMT

Anonymous , 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")

Re: Find upper case string in multiple columns

Anonymous — Tue, 06 Dec 2022 13:16:23 GMT

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

Re: Find upper case string in multiple columns

Anonymous — Tue, 06 Dec 2022 13:36:42 GMT

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"
)

Re: Find upper case string in multiple columns

amitchandak — Tue, 06 Dec 2022 13:42:43 GMT

Anonymous , Try like

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

Re: Find upper case string in multiple columns

Anonymous — Tue, 06 Dec 2022 13:50:22 GMT

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"))

Re: Find upper case string in multiple columns

Anonymous — Tue, 06 Dec 2022 15:28:55 GMT

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.

Re: Find upper case string in multiple columns

Anonymous — Thu, 08 Dec 2022 12:58:20 GMT

@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.

Re: Find upper case string in multiple columns

v-zhangti — Fri, 09 Dec 2022 01:54:56 GMT

Hi, Anonymous

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.

Re: Find upper case string in multiple columns

Anonymous — Fri, 16 Dec 2022 19:56:26 GMT

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?