Color Coding Rows

[JohnW]

Hi everyone!

I'm a little stuck on this issue and I'm not sure of a way around it.  I need to color code the rows in a matrix to show what IDs are relevant to each other.  For example IDs 10, 20 and 30 are all relevant to each other and need to be the same color while IDs 5, 15 and 25 are relevant to each other and need to be the same color but a different color from IDs 10, 20 and 30.  There are more than just these two but I just wanted to show an example.  Thanks for the help!

10	RED
20	RED
5	BLUE
30	RED
15	BLUE
25	BLUE

[mahoneypat · Accepted solution]

You can make a simple measure like this (using your sample data table).  Any measure that returns a color in text format will work.

Text Color = MIN(Colors[Color])

And then use it to set up conditional formatting on the Font Color to get the result shown.

Regards,

Pat

[amitchandak · Accepted solution]

@JohnW , Create a color measure like

color Measure =
Switch(True() ,
max(Table[ID]) in {10,20,30} ,"Red",
max(Table[ID]) in {5,15,25} ,"Blue",
"White"
)

And use that in conditional formatting using the "Field Value" option

Refer for Steps: https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

[PhilipTreacy · Accepted solution]

Hi @JohnW 

Please check this sample PBIX file

I assume you don't have a column with the color names in it, just a column of numbers?  In my example I've added a few extra numbers so you can see how it works.  You haven't specified what the relationship between ID's is so can'tprogram that in at this point so I'm assuming all numbers ending in 0 are related, all numbers ending in 5 are related - that's what is in your example.

You can create a measure to set the row color based on the value of the ID

RowColor = SWITCH(
    
    TRUE(),
    
    MOD(MIN('Table'[ID]),10) = 0, "red",

    MOD(MIN('Table'[ID]),10) = 3, "green",

    MOD(MIN('Table'[ID]),10) = 5, "blue",

    "black"

)

or the text color

TextColor = SWITCH(
    
    TRUE(),
    
    MOD(MIN('Table'[ID]),10) = 0, "red",

    MOD(MIN('Table'[ID]),10) = 3, "green",

    MOD(MIN('Table'[ID]),10) = 5, "blue",

    "black"

)

 

In the Conditional Formatting for the ID set the Background or Font color, set it to Format By the Field Value (the measures) RowColor or TextColor.

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[amitchandak · Accepted solution]

@JohnW , Create a color measure like

color Measure =
Switch(True() ,
max(Table[ID]) in {10,20,30} ,"Red",
max(Table[ID]) in {5,15,25} ,"Blue",
"White"
)

And use that in conditional formatting using the "Field Value" option

Refer for Steps: https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

[JohnW]

Hi @amitchandak !

Your solution works perfectly for me but is there a way to have the rows show just the color and not show the words of that color? Thanks for the help!

[JohnW]

@amitchandak ,

I figured it out, just needed to a bit more reading 😂, thanks again for the help!

[mahoneypat · Accepted solution]

You can make a simple measure like this (using your sample data table).  Any measure that returns a color in text format will work.

Text Color = MIN(Colors[Color])

And then use it to set up conditional formatting on the Font Color to get the result shown.

Regards,

Pat