cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zia1
Frequent Visitor

Compare Values across columns in table

Hi,

I wish to compare 3 columns of values in a table and return a True/False if they all match.

I am using the following formula:

NewColumn = Table1[Column1]=Table1[Column2] && Table1[Column1]=Table1[Column3] && Table1[Column2]=Table1[Column3]

This works if all 3 columns have a value. But i want it to ignore blanks e.g.

 

Column1Column2Column3NewColumn
777TRUE
787FALSE
887FALSE
77 TRUE
 7 TRUE
1 ACCEPTED SOLUTION
AlB
Super User III
Super User III

Hi @zia1 

NewColumn =
(
    Table1[Column1] = Table1[Column2]
        || ISBLANK ( Table1[Column1] )
        || ISBLANK ( Table1[Column2] )
)
    && (
        Table1[Column1] = Table1[Column3]
            || ISBLANK ( Table1[Column1] )
            || ISBLANK ( Table1[Column3] )
    )
    && (
        Table1[Column2] = Table1[Column3]
            || ISBLANK ( Table1[Column2] )
            || ISBLANK ( Table1[Column3] )
    )

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

View solution in original post

2 REPLIES 2
AlB
Super User III
Super User III

Hi @zia1 

NewColumn =
(
    Table1[Column1] = Table1[Column2]
        || ISBLANK ( Table1[Column1] )
        || ISBLANK ( Table1[Column2] )
)
    && (
        Table1[Column1] = Table1[Column3]
            || ISBLANK ( Table1[Column1] )
            || ISBLANK ( Table1[Column3] )
    )
    && (
        Table1[Column2] = Table1[Column3]
            || ISBLANK ( Table1[Column2] )
            || ISBLANK ( Table1[Column3] )
    )

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

View solution in original post

zia1
Frequent Visitor

Thank for the quick reply.  Just checked and it works!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors