Forum Discussion

melimob1's avatar
melimob1
Helper I
1 year ago

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

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 APP...
Need Help
Bibiano_Geraldo's avatar
Bibiano_Geraldo
Super User
1 year ago

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's avatar
    melimob1
    Helper I
    1 year ago

    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's avatar
      Bibiano_Geraldo
      Super User
      1 year ago

      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.