cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## How to compare text across multiple columns

Hi,

How do i compare text across multiple columns ignoring any blanks and ignoring case.

e.g.

 Column1 Column2 Column3 Column4 Column5 Expected Result cat cat cat cat cat TRUE cat Cat CAT cat cat TRUE cat cat TRUE cat cat cater cat cat FALSE

In Excel, i used the following formula and it worked fine:

=SUMPRODUCT((A2:E2<>"")/COUNTIF(A2:E2,A2:E2&""))=1

Thanks for any help.

1 ACCEPTED SOLUTION
Super User
Result =
var a= {[Column1],[Column2],[Column3],[Column4],[Column5]}
var b=FILTER(a,[Value]<>"")
return COUNTROWS(DISTINCT(b))=1

or more brutish:
Result = COUNTROWS(FILTER(DISTINCT({[Column1],[Column2],[Column3],[Column4],[Column5]}),[Value]<>""))=1
4 REPLIES 4
Super User
Result =
var a= {[Column1],[Column2],[Column3],[Column4],[Column5]}
var b=FILTER(a,[Value]<>"")
return COUNTROWS(DISTINCT(b))=1

or more brutish:
Result = COUNTROWS(FILTER(DISTINCT({[Column1],[Column2],[Column3],[Column4],[Column5]}),[Value]<>""))=1
Anonymous
Not applicable

Both Solutions work.  Thank You so much.

Super User

here's a fun way:

= SWITCH(TRUE(),[Column1]<>[Column2],FALSE(),[Column1]<>[Column3],FALSE(),[Column1]<>[Column4],FALSE(),[Column1]<>[Column5],FALSE(),TRUE())

Anonymous
Not applicable

Doesn't seem to work if there are any blank columns.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors