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 ACompany B
UKAPPLESORANGES
USAAPPLESAPPLES
CANADAPEARSORANGES
FRANCEUNSPECIFIEDUNSPECIFIED

 

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

 

Need Help

8 Replies

    • melimob1's avatar
      melimob1
      Helper I
      1 year ago

      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 

  • 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. 

         

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

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

      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's avatar
        Anonymous
        Not applicable
        1 year ago

        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