Forum Discussion

JinNeedsHelp's avatar
JinNeedsHelp
Regular Visitor
1 year ago
Solved

Format Matrix with Specific Colour based on Cells

Hi, 

I am seeking help to create a migration matrix that will provide specific colouring based on cell (irregardless of the value of that specific cell). What we intend to show in the matrix is that if it moves from Rating 1 to Rating 4 it will be red (e.g. 10 counts are in that cell). Similarly, if 10 counts move from Rating 4 to Rating 1 it will be green. But if there were no counts in Rating 3 to Rating 1 we will still maintain that cell as green (red if it is the other way round). 

As an example I have included a picture to illustrate how it should looks like. 

 

 

 

 

 

 

What should I do to achieve this result? Appreciate any feedbacks or help from the community since I have no clue on how to tackle this issue. 

Need Help
  • Ritaf1983's avatar
    Ritaf1983
    1 year ago

    Hi JinNeedsHelp 
    You can create Dax code like :

    color =
    var row_selection = int(RIGHT(SELECTEDVALUE('Table'[Row Label]),1))
    VAR column_selection =int(RIGHT(SELECTEDVALUE('Table'[Column Label]),1))
    Return
    IF(row_selection=column_selection,"Grey",
    If(row_selection = column_selection+1,"#FF7F7F",
    If(row_selection = column_selection-1,"#90EE90",
    IF(row_selection < column_selection+2,"Green",

    "Red"
    ))))
    And use it for conditional formatting

    Result

    The pbix is attached,
    You can use other colors according to your needs

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

     

5 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    1 year ago

    Hi JinNeedsHelp 
    You can create Dax code like :

    color =
    var row_selection = int(RIGHT(SELECTEDVALUE('Table'[Row Label]),1))
    VAR column_selection =int(RIGHT(SELECTEDVALUE('Table'[Column Label]),1))
    Return
    IF(row_selection=column_selection,"Grey",
    If(row_selection = column_selection+1,"#FF7F7F",
    If(row_selection = column_selection-1,"#90EE90",
    IF(row_selection < column_selection+2,"Green",

    "Red"
    ))))
    And use it for conditional formatting

    Result

    The pbix is attached,
    You can use other colors according to your needs

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

     

  • JinNeedsHelp's avatar
    JinNeedsHelp
    Regular Visitor
    1 year ago

    Hi Rita, 

    Thank you so much ! I really appreciate your solution. I am able to show the right table for PBI. 
    On a side note, if I want to change the colour of the cell it will be based on the hex colour codes ?

    • Ritaf1983's avatar
      Ritaf1983
      Super User
      1 year ago

      Hi again 🙂

      I'm glad to hear the solution worked for you!

      Regarding changing the cell color in DAX: Yes, you can use either hex color codes (e.g., "#FF5733") or standard color names (e.g., "Red") in the formula. However, keep in mind that when you use color names instead of hex codes, you won't have precise control over the specific shade since the engine decides the exact variant of the color.

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

       

      • JinNeedsHelp's avatar
        JinNeedsHelp
        Regular Visitor
        1 year ago

        Hi Rita, 

        You have been a great help! One last question from me regarding this, is there a way to show the value of the cell along with the row percentage? 

        Let's take your value as an example: 

        Can I show the table as 100 (28.16%) in rating 1 by rating 1 cell? [28.16 = 100/sum of row of rating 1]