Reply
avatar user
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.

 

Column1Column2Column3Column4Column5Expected Result
catcatcatcatcatTRUE
catCatCATcatcatTRUE
 catcat  TRUE
catcatcatercatcatFALSE

 

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
lbendlin
Super User
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

View solution in original post

4 REPLIES 4
lbendlin
Super User
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
avatar user
Anonymous
Not applicable

Both Solutions work.  Thank You so much.

lbendlin
Super User
Super User

here's a fun way:

 

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

avatar user
Anonymous
Not applicable

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

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)