Custom Background Color Based Off Multiple Conditions

[jdwalker5]

Hello,

I am trying to apply a background color to cells in a matrix if multiple conditions are met.  The data is set up like this:

Region	Count
1	389
2	157
3	227
4	63
5	174
6	349

The background color needs to be green if the count < x, yellow if > x and < y, and red if > y and < z.

x, y and z will be different based off of day of week and region.

I created the below column using the SWITCH(TRUE() function, but when I format by that column the background is always green.  I think that is because the column is data type Text which forces me to choose either First or Last as a summarization.  I think the logic in the DAX is good but I don't know how to get around the summarization.  Any ideas on how I can get this to work?

Card Color =
SWITCH(
TRUE(),
'IB Data'[Day Sort] = 1 && [Scheduled Today] <= 130, "#4CB70E",
'IB Data'[Day Sort] = 1 && [Scheduled Today] > 130 && [Scheduled Today] <= 160, "#EBEB0C",
'IB Data'[Day Sort] = 1 && [Scheduled Today] > 160, "#EA5C69",
'IB Data'[Day Sort] = 2 && [Scheduled Today] <= 130, "#4CB70E",
'IB Data'[Day Sort] = 2 && [Scheduled Today] > 130 && [Scheduled Today] <= 150, "#EBEB0C",
'IB Data'[Day Sort] = 2 && [Scheduled Today] > 150, "#EA5C69",
'IB Data'[Day Sort] IN { 3, 5 } && [Scheduled Today] <= 250, "#4CB70E",
'IB Data'[Day Sort] IN { 3, 5 } && [Scheduled Today] > 250 && [Scheduled Today] <= 300, "#EBEB0C",
'IB Data'[Day Sort] IN { 3, 5 } && [Scheduled Today] > 300, "#EA5C69",
'IB Data'[Day Sort] = 4 && [Scheduled Today] <= 220, "#4CB70E",
'IB Data'[Day Sort] = 4 && [Scheduled Today] > 220 && [Scheduled Today] <= 300, "#EBEB0C",
'IB Data'[Day Sort] = 4 && [Scheduled Today] > 300, "#EA5C69",
'IB Data'[Day Sort] = 6 && [Scheduled Today] <= 250, "#4CB70E",
'IB Data'[Day Sort] = 6 && [Scheduled Today] > 250 && [Scheduled Today] <= 320, "#EBEB0C",
'IB Data'[Day Sort] = 6 && [Scheduled Today] > 320, "#EA5C69",
'IB Data'[Day Sort] = 7 && [Scheduled Today] <= 250, "#4CB70E",
'IB Data'[Day Sort] = 7 && [Scheduled Today] > 250 && [Scheduled Today] <= 330, "#EBEB0C", "#FFFFFF")

FYI, the column Day Sort represents the day of the week.  1 = Saturday, 2 = Sunday, and so on.

[Anand24 · Accepted solution]

Hi @jdwalker5 ,
You will need to conditionally format the background and it should work irrespective of First or Last.

The DAX seems appropriate for the color code.

It would be great if you share the .pbix so that we can work on it and get this resolved.

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!! Proud To Be a Super User !!! LinkedIn

[Anand24 · Accepted solution]

Hi @jdwalker5 ,
You will need to conditionally format the background and it should work irrespective of First or Last.

The DAX seems appropriate for the color code.

It would be great if you share the .pbix so that we can work on it and get this resolved.

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!! Proud To Be a Super User !!! LinkedIn

[jdwalker5]

Unfortunately, I cannot share the pbix because the data is proprietary to the company that I work for.  The data in the table that I provided should work to find a solution.

[Anand24 · Accepted solution]

@jdwalker5 ,
There is a bit of change in the DAX that should work for you. You haven't used any aggregation(sum, avg, max, etc.) in your dax and that's why PowerBI is unable to correctly identify the values.

I created a calculated measure with minor changes and it worked.

Reference Solution image on data you provided(I changed Region 6's count to 280 to get yellow color):

Below is the DAX I used:

Card Color =
SWITCH(
TRUE(),
MAX('Test_Table'[Region]) = "1" && SUM('Test_Table'[Count]) <= 130, "#4CB70E",
MAX('Test_Table'[Region]) = "1" && SUM('Test_Table'[Count]) > 130 && SUM('Test_Table'[Count]) <= 160, "#EBEB0C",
MAX('Test_Table'[Region]) = "1" && SUM('Test_Table'[Count]) > 160, "#EA5C69",
MAX('Test_Table'[Region]) = "2" && SUM('Test_Table'[Count]) <= 130, "#4CB70E",
MAX('Test_Table'[Region]) = "2" && SUM('Test_Table'[Count]) > 130 && SUM('Test_Table'[Count]) <= 150, "#EBEB0C",
MAX('Test_Table'[Region]) = "2" && SUM('Test_Table'[Count]) > 150, "#EA5C69",
MAX('Test_Table'[Region]) IN { "3", "5" } && SUM('Test_Table'[Count]) <= 250, "#4CB70E",
MAX('Test_Table'[Region]) IN { "3", "5" } && SUM('Test_Table'[Count]) > 250 && SUM('Test_Table'[Count]) <= 300, "#EBEB0C",
MAX('Test_Table'[Region]) IN { "3", "5" } && SUM('Test_Table'[Count]) > 300, "#EA5C69",
MAX('Test_Table'[Region]) = "4" && SUM('Test_Table'[Count]) <= 220, "#4CB70E",
MAX('Test_Table'[Region]) = "4" && SUM('Test_Table'[Count]) > 220 && SUM('Test_Table'[Count]) <= 300, "#EBEB0C",
MAX('Test_Table'[Region]) = "4" && SUM('Test_Table'[Count]) > 300, "#EA5C69",
MAX('Test_Table'[Region]) = "6" && SUM('Test_Table'[Count]) <= 250, "#4CB70E",
MAX('Test_Table'[Region]) = "6" && SUM('Test_Table'[Count]) > 250 && SUM('Test_Table'[Count]) <= 320, "#EBEB0C",
MAX('Test_Table'[Region]) = "6" && SUM('Test_Table'[Count]) > 320, "#EA5C69",
MAX('Test_Table'[Region]) = "7" && SUM('Test_Table'[Count]) <= 250, "#4CB70E",
MAX('Test_Table'[Region]) = "7" && SUM('Test_Table'[Count]) > 250 && SUM('Test_Table'[Count]) <= 330, "#EBEB0C", "#FFFFFF"
)

Also attached is the pbix file with solution.

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!! Proud To Be a Super User !!! LinkedIn

[jdwalker5]

Thank you, Anand24!  This looks to have solve my issue!