Conditional Format when value does not equal same row value in another column (Matrix)

[melimob1]

Hi @Bibiano_Geraldo / all

I have a matrix which shows by country, which top product two different clients are using.

E.g.

	Company A	Company B
UK	APPLES	ORANGES
USA	APPLES	APPLES
CANADA	PEARS	ORANGES
FRANCE	UNSPECIFIED	UNSPECIFIED

Apply colours to Column A only where:

If A doesn't = B, red

If A does = B, Green

exception... where A or B = "UNSPECIFIED", highlight that cell also 

Can you let me know how I can acheive this the easiest way?

Many thanks

[Anonymous]

Hi @melimob1 ,
Thanks for Bibiano_Geraldo and FreemanZ reply.
First you can try the follwing code

SameProductMeasure = 
VAR ProductsList = 
    CALCULATETABLE(
        VALUES('Table'[Product]),
        ALLEXCEPT('Table', 'Table'[Country])
    )
VAR HasUnspecified = 
    CALCULATE(
        COUNTROWS('Table'),
        'Table'[Product] = "UNSPECIFIED",
        ALLEXCEPT('Table', 'Table'[Country])
    )
RETURN
IF(
    SELECTEDVALUE('Table'[Company]) = "CompanyA",
    IF(
        HasUnspecified > 0,
        "brown",
        IF(
            COUNTROWS(ProductsList) = 1,
            "Red",
            "Green"
        )
    )
)

Right click the values and set conditional format

Final output

Secondly there is no way to customize the formatting of line headings in power bi. However, if you want to show the same rows, you can create a new table visualization to show the corresponding cases

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[melimob1]

Hey, thank you also for your reply.  It's given me a result but unfortunatley, not exactly what I need. 

If in Australia, Company A has the same product as company B, then show as green.

I have instances in this result where it's showing green and they don't match 😞 

thank you so much anyhow!

[Anonymous]

Hi @melimob1 ,

Thanks for your reply, can you show more sample data so that  we can correct our logic of the code. Please hide sensitive information in advance.

Best regards,
Albert He

[Bibiano_Geraldo]

Hi @melimob1 ,

Considering that you data look like this:

And your Matrix Like this:

Create a measure by this DAX:

Color Coding Measure = 
VAR CurrentCountry = SELECTEDVALUE('Table'[Country])  -- Get current country context
VAR ProductA = 
    CALCULATE(
        SELECTEDVALUE('Table'[Product]),
        'Table'[Clients] = "Company A",  -- Filter for Company A
        'Table'[Country] = CurrentCountry
    )
VAR ProductB = 
    CALCULATE(
        SELECTEDVALUE('Table'[Product]),
        'Table'[Clients] = "Company B",  -- Filter for Company B
        'Table'[Country] = CurrentCountry
    )
RETURN
    SWITCH(
        TRUE(),
        ISBLANK(ProductA) || ISBLANK(ProductB) || SELECTEDVALUE('Table'[clients]) <> "Company A", BLANK(), -- Avoid errors for blank rows
        ProductA = "Unspecified" || ProductB = "Unspecified", 2, -- Yellow for Unspecified
        ProductA = ProductB, 1, -- Green for matching
        ProductA <> ProductB, 3 -- Red for mismatching
    )

Right click on top of your products values fields in the Matrix and add a background color condition:

On the prompted window, please add these rules and hit OK:

Now your table should look like this:

Let me know if you're getting any issue.

[melimob1]

thank you so much again however I tried this and nothing happens.

You are correct my data is structured like that however the country + client could appear multiple times with each product, so you may have CANADA + CLIENT A, 3 rows as they have apples, pears and oranges.

I've therefore summarised a column which finds the MAX for client, country which just contains the value I want to compare.  this is DOMINANT PRODUCT. 

For my matrix, yes, it ends up looking like you have shown.

I'm thinking also, if both match, I would like to highlight both columns Greeen and or the country. is that easier or harder as I don't want to complicate too much.

thank you agaiN!

[Bibiano_Geraldo]

Hi @melimob1,

To highlight both columns just remove the condition <> a in the measure, please, see updated DAX:

Color Coding Measure = 
VAR CurrentCountry = SELECTEDVALUE('Table'[Country])  -- Get current country context
VAR ProductA = 
    CALCULATE(
        SELECTEDVALUE('Table'[Product]),
        'Table'[Clients] = "Company A",  -- Filter for Company A
        'Table'[Country] = CurrentCountry
    )
VAR ProductB = 
    CALCULATE(
        SELECTEDVALUE('Table'[Product]),
        'Table'[Clients] = "Company B",  -- Filter for Company B
        'Table'[Country] = CurrentCountry
    )
RETURN
    SWITCH(
        TRUE(),
        ProductA = "Unspecified" || ProductB = "Unspecified", 2, -- Yellow for Unspecified
        ProductA = ProductB, 1, -- Green for matching
        ProductA <> ProductB, 3 -- Red for mismatching
    )

Let me know if work, if not, please provide more data. 

[FreemanZ]

hi @melimob1 ,

try to apply comditional formatting with measure like:

colormeasure =

VAR _b=

CALCULATE(

[measure],

data[country]="Country N"

)

RETURN

IF([measure]=_b, "red", "green")

more info

https://community.fabric.microsoft.com/t5/Power-BI-Community-Blog/Conditional-Formatting-Using-Custo...

[melimob1]

Hi, thank you for your response but I'm not sure I follow... 

Does this mean I would have to list all my countries as I have lots?

Besides, it not really the country I am comparing (sorry just read my post and where I said 'column A' I meant 'Company A'.  It's the products of that country row text to text columns under the company I want to compare? 

not sure what measure I would need to add in where it states "Measure"?  I need a measure formula to state, if Column x = column y value, then mark red. 

Many thanks

Melissa