How to compare two columns in power bi

I have a table that contains two string/ text columns.

I would like to check if the same "Value"/ Text is in the column, but how in Power Query?

I am trying to compare two columns in Power BI and only color when there is a difference from one column to another.

Like this:

The pictures are from:
//community.powerbi.com/t5/Desktop/Compare-two-columns-in-a-table-visual/m-p/272761#M122058

I saw the solution there (the link above), but since it was asked two years ago, I wanted to check if there is a better solution or color formatting I am not aware of.

I have to compare over 70 pairs of columns. That is why I was checking if there is an alternative that does not require to create a column per pair

Or how would you approach it?

I really appreciate any help you can provide.

2

If you want this to be achieved through a DAX calculated column

Column C = IF ( ( 'Table'[column B] ) IN VALUES ( 'Table'[column A] ), "Yes", "No" )

If you want this to be achieved through a DAX measure

Measure = VAR _lookUP = CALCULATE ( MAX ( 'Table'[column A] ), FILTER ( ALL ( 'Table' ), ( 'Table'[column A] ) IN SUMMARIZE ( 'Table', 'Table'[column B] ) ) ) RETURN IF(_lookUP=BLANK(),"No","Yes")

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.

Hi @abhay03,Until now, custom color isn't supported in Power BI, please review the feature requests and vote them. 

//ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/17339728-more-flexible-conditiona...

//ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/16417966-conditional-formating-fo...

For your requirement, you can create a calculated column using the formula below.

Flag = IF(Test[Sales-DB]=Test[Sales_File],1,0)

Then you can use conditional formatting to set the color of Flag column as follows.

You will get the result.

Best Regards,

Angelia

View solution in original post