Reply
Topic Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
Anonymous
Not applicable
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to compare text across multiple columns
01-21-2021
04:32 AM
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.
Solved! Go to Solution.
1 ACCEPTED SOLUTION
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-22-2021
07:44 AM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-22-2021
07:44 AM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-22-2021
10:47 AM
Both Solutions work. Thank You so much.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-21-2021
04:53 PM
here's a fun way:
= SWITCH(TRUE(),[Column1]<>[Column2],FALSE(),[Column1]<>[Column3],FALSE(),[Column1]<>[Column4],FALSE(),[Column1]<>[Column5],FALSE(),TRUE())
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-22-2021
01:59 AM
Doesn't seem to work if there are any blank columns.
Helpful resources
Announcements
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
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.
Recommendations
Subject | Author | Posted | |
---|---|---|---|
09-10-2024 08:33 AM | |||
04-03-2024 08:42 AM | |||
03-06-2024 02:12 PM | |||
09-04-2024 04:51 AM | |||
01-09-2023 11:58 PM |
Top Solution Authors (Last Month)
User | Count |
---|---|
13 | |
12 | |
11 | |
7 | |
7 |
Top Kudoed Authors (Last Month)
User | Count |
---|---|
16 | |
13 | |
11 | |
11 | |
9 |