Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
creyssonbr
New Member

Comparing two columns and set colors for specific match in Power BI

Hi,

I want to retrieve a value from two common rows and use if to return color, for conditional formatting in Power BI, as the following requirement:

  1. If content doesn't show on table 1 and doesn't show on table 2, set black.
  2. If content shows on table 1 and not on table 2 and age1 is less than 10, set yellow.
  3. If content shows on table 1 and not on table 2 and age1 is equal to 10, set purple.
  4. If content shows on table 1 and not on table 2 and age1 is more than 10, set red.
  5. If content shows on table 1 and on table 2, set green.

Content is text. Age is whole number.

I tried the following measure, but something is wrong with IF statement. I'm not sure if the SELECTEDVALUE is the right funtion to use here. I'm open to any suggestion and thanks a lot for your help.

Content Check = 
VAR C1= SELECTEDVALUE (Table1[Content]) 
VAR C2= SELECTEDVALUE (Table2[Content]) 
VAR Age SELECTEDVALUE (Table1[Age]) 
VAR Color= "#000000" #Black by default
IF (Invoice <> Journal, 
    Color="00FF00", 
    IF (Age > 10, 
        Color "#FF0000", 
        IF (Age <10, 
            Color="#FFFF00", 
            Color="#FF00FF"))) 

RETURN Color

Table1

|      Content        |     Age    |
|---------------------|------------|
|          Bravo      |         1  |
|          Alpha      |         5  | 
|          Charlie    |         10 |
|          Tango      |         3  |
|          Delta      |         22 |
|          Golf       |         42 |

Table2

|      Content        |  
|---------------------|
|          Alpha      |
|          Charlie    | 
|          Delta      | 
|          Golf       |  

The point here is to scan every row in table one and determine if it's there in table 2 too. If not, will return RGB Black. If it's there, it will be comparing Table1 Age, to check what color should be set.

1 ACCEPTED SOLUTION
harshnathani
Community Champion
Community Champion

HI @creyssonbr ,

 

Create a Calculated Column in Table A

 

ContentTableB = LOOKUPVALUE(TableB[Content],TableB[Content],TableA[Content])

 

You can use this measure

 

Measure = SWITCH
(
    TRUE(),
     MAX(TableA[ContentTableB]) = BLANK() && MAX(TableA[Age]) < 10,"Yellow",
     MAX(TableA[ContentTableB]) = BLANK() && MAX(TableA[Age]) = 10,"Purple",
     MAX(TableA[ContentTableB]) = BLANK() && MAX(TableA[Age]) = 10,"Red",
     MAX(TableA[ContentTableB]) = MAX(TableA[Content]),"Green",
     "Black"
     )

 

 

1.JPG

 

Follow the stps:

 

1.JPG11.JPG

 

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

View solution in original post

2 REPLIES 2
creyssonbr
New Member

Worked like a charm!

Thank you so much for this perfect and detailed solution Harsh.

harshnathani
Community Champion
Community Champion

HI @creyssonbr ,

 

Create a Calculated Column in Table A

 

ContentTableB = LOOKUPVALUE(TableB[Content],TableB[Content],TableA[Content])

 

You can use this measure

 

Measure = SWITCH
(
    TRUE(),
     MAX(TableA[ContentTableB]) = BLANK() && MAX(TableA[Age]) < 10,"Yellow",
     MAX(TableA[ContentTableB]) = BLANK() && MAX(TableA[Age]) = 10,"Purple",
     MAX(TableA[ContentTableB]) = BLANK() && MAX(TableA[Age]) = 10,"Red",
     MAX(TableA[ContentTableB]) = MAX(TableA[Content]),"Green",
     "Black"
     )

 

 

1.JPG

 

Follow the stps:

 

1.JPG11.JPG

 

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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