cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
SnymanGrobler
Regular Visitor

Compare 2 Columns, Return 3 values

Hi,

I want to compare two columns on the same table.

 

If the Columns match it should return 3.

If thet dont match return 2.

If both are blank return 1.

 

I want to use these values in conditional formatting. 1 for red, 2 for yellow and 3 for green.

 

I have tried this, but it wont return 1. Only returns 2 and 3.

Match = SWITCH(
    TRUE(),
   Table[Column1] = Table[Column2], 3,
   ISBLANK(Table[Column1]) && ISBLANK(Table[Column2]), 1,
    TRUE(), 2
)
 
Can someone please help, thanks.
1 ACCEPTED SOLUTION

@SnymanGrobler 
Try

Match =
SWITCH (
    TRUE (),
    Table[Column1] = BLANK ( ) && Table[Column2] = BLANK ( ), 1,
    Table[Column1] = Table[Column2], 3,
    2
)

or

Match =
SWITCH (
    TRUE (),
    Table[Column1] & Table[Column2] = "", 1,
    Table[Column1] = Table[Column2], 3,
    2
)

View solution in original post

6 REPLIES 6
tamerj1
Super User
Super User

Hi @SnymanGrobler 
Please try

Match =
SWITCH (
    TRUE (),
    ISBLANK ( Table[Column1] ) && ISBLANK ( Table[Column2] ), 1,
    Table[Column1] = Table[Column2], 3,
    2
)

Hi, @tamerj1 

This does not work.

My conditional formatting looks as follows:

SnymanGrobler_0-1687349992985.png

With this logic it still only returns green and yellow colors. Example:

SnymanGrobler_1-1687350064160.png

Do you have any idea why this is the case?

Thanks

 

@SnymanGrobler 
Try

Match =
SWITCH (
    TRUE (),
    Table[Column1] = BLANK ( ) && Table[Column2] = BLANK ( ), 1,
    Table[Column1] = Table[Column2], 3,
    2
)

or

Match =
SWITCH (
    TRUE (),
    Table[Column1] & Table[Column2] = "", 1,
    Table[Column1] = Table[Column2], 3,
    2
)

Thank you very much, second solution worked.

devanshi
Helper V
Helper V

Match = IF(ISBLANK(Table[Column1]) && ISBLANK(Table[Column2]), 1
                       IF(Table[Column1] = Table[Column2], 3, 2)
                   )

SnymanGrobler
Regular Visitor

@Greg_Deckler Please help

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors