Conditional formatting using DAX

[nish18_1990]

Hi ,

I want to do conditional formatting on the values based on the criteria for each country .

I have data like below :

Category	Quantity Q2	country
Chairs	10	China
Tables	20	China
Cars	30	China
Bikes	40	China
Chairs	13	India
Tables	22	India
Cars	54	India
Bikes	60	India

Conditions are :

For China : 

if >=10 then green ,less than 10 and greater than 6 then amber else red

if >=30 then green ,less than 29 and greater than 19 then amber else red

if >=60 then green ,less than 60 and greater than 50 then amber else red

if >=60 then green ,less than 60 and greater than 35 then amber else red

For India:

if >=12 then green ,less than 10 and greater than 5 then amber else red

if >=25 then green ,less than 20 and greater than 19 then amber else red

if >=60 then green ,less than 60 and greater than 50 then amber else red

if >=60 then green ,less than 60 and greater than 35 then amber else red

Looking for output like below :

[SamWiseOwl · Accepted solution]

Hi @nish18_1990 
Colour measure =

var Sales = Sum(table[Quantity])

RETURN
Switch(

          SELECTEDVALUE(table[Country])

         ,"China"

         ,Switch(
                      SELECTEDVALUE(table[CATEGORY])
                    ,"Chairs", Switch(true(), Sales >= 10, "Green", Sales > 6, "Yellow", "Red")

                   ,"Tables", Switch(true(), Sales >= 30, "Green", Sales > 19, "Yellow", "Red")
             )

 ,"India"

         ,Switch(
                      SELECTEDVALUE(table[CATEGORY])
                    ,"Chairs", Switch(true(), Sales >= 10, "Green", Sales > 6, "Yellow", "Red")

                   ,"Tables", Switch(true(), Sales >= 30, "Green", Sales > 19, "Yellow", "Red")
             )
)

[nish18_1990]

Thank you ! this solution worked 

[bhanu_gautam]

@nish18_1990 Use the following DAX formula to create a new column that will hold the color values based on the conditions provided.

DAX
Color =
SWITCH(
TRUE(),
'Table'[country] = "China" && 'Table'[Category] = "Chairs" && 'Table'[Quantity Q2] >= 10, "Green",
'Table'[country] = "China" && 'Table'[Category] = "Chairs" && 'Table'[Quantity Q2] < 10 && 'Table'[Quantity Q2] > 6, "Amber",
'Table'[country] = "China" && 'Table'[Category] = "Chairs" && 'Table'[Quantity Q2] <= 6, "Red",
'Table'[country] = "China" && 'Table'[Category] = "Tables" && 'Table'[Quantity Q2] >= 30, "Green",
'Table'[country] = "China" && 'Table'[Category] = "Tables" && 'Table'[Quantity Q2] < 30 && 'Table'[Quantity Q2] > 19, "Amber",
'Table'[country] = "China" && 'Table'[Category] = "Tables" && 'Table'[Quantity Q2] <= 19, "Red",
'Table'[country] = "China" && 'Table'[Category] = "Cars" && 'Table'[Quantity Q2] >= 60, "Green",
'Table'[country] = "China" && 'Table'[Category] = "Cars" && 'Table'[Quantity Q2] < 60 && 'Table'[Quantity Q2] > 50, "Amber",
'Table'[country] = "China" && 'Table'[Category] = "Cars" && 'Table'[Quantity Q2] <= 50, "Red",
'Table'[country] = "China" && 'Table'[Category] = "Bikes" && 'Table'[Quantity Q2] >= 60, "Green",
'Table'[country] = "China" && 'Table'[Category] = "Bikes" && 'Table'[Quantity Q2] < 60 && 'Table'[Quantity Q2] > 35, "Amber",
'Table'[country] = "China" && 'Table'[Category] = "Bikes" && 'Table'[Quantity Q2] <= 35, "Red",
'Table'[country] = "India" && 'Table'[Category] = "Chairs" && 'Table'[Quantity Q2] >= 12, "Green",
'Table'[country] = "India" && 'Table'[Category] = "Chairs" && 'Table'[Quantity Q2] < 10 && 'Table'[Quantity Q2] > 5, "Amber",
'Table'[country] = "India" && 'Table'[Category] = "Chairs" && 'Table'[Quantity Q2] <= 5, "Red",
'Table'[country] = "India" && 'Table'[Category] = "Tables" && 'Table'[Quantity Q2] >= 25, "Green",
'Table'[country] = "India" && 'Table'[Category] = "Tables" && 'Table'[Quantity Q2] < 20 && 'Table'[Quantity Q2] > 19, "Amber",
'Table'[country] = "India" && 'Table'[Category] = "Tables" && 'Table'[Quantity Q2] <= 19, "Red",
'Table'[country] = "India" && 'Table'[Category] = "Cars" && 'Table'[Quantity Q2] >= 60, "Green",
'Table'[country] = "India" && 'Table'[Category] = "Cars" && 'Table'[Quantity Q2] < 60 && 'Table'[Quantity Q2] > 50, "Amber",
'Table'[country] = "India" && 'Table'[Category] = "Cars" && 'Table'[Quantity Q2] <= 50, "Red",
'Table'[country] = "India" && 'Table'[Category] = "Bikes" && 'Table'[Quantity Q2] >= 60, "Green",
'Table'[country] = "India" && 'Table'[Category] = "Bikes" && 'Table'[Quantity Q2] < 60 && 'Table'[Quantity Q2] > 35, "Amber",
'Table'[country] = "India" && 'Table'[Category] = "Bikes" && 'Table'[Quantity Q2] <= 35, "Red",
BLANK()
)

Go to the "Visualizations" pane and select the table or matrix visual where you want to apply the conditional formatting.
Click on the "Format" pane (paint roller icon).
Expand the "Conditional formatting" section.
Select the field you want to format (e.g., Quantity Q2).
Click on "Background color" or "Font color".
Choose "Field value" and select the new column (Color) you created for the conditional formatting.