conditional formatting

[dhrupal_shah]

I want to apply conditional formatting based of column value with range. e.g. in Central if value between 1 to 10000 then red and 10000 to 15000 then green, in East value between 1 to 2000 then red and 2000 to 5000 with yellow and >5000 then green. this require because some time new state launch and criteria are diffrent please guide how to do the same.

[darkniqht]

ReportStatus = 
SWITCH(
    TRUE(),
    'YourTable'[Region] = "Central" && 'YourTable'[Value] >= 1 && 'YourTable'[Value] <= 10000, "Red",
    'YourTable'[Region] = "Central" && 'YourTable'[Value] > 10000 && 'YourTable'[Value] <= 15000, "Green",
    'YourTable'[Region] = "East" && 'YourTable'[Value] >= 1 && 'YourTable'[Value] <= 2000, "Red",
    'YourTable'[Region] = "East" && 'YourTable'[Value] > 2000 && 'YourTable'[Value] <= 5000, "Yellow",
    'YourTable'[Region] = "East" && 'YourTable'[Value] > 5000, "Green",
    "No Color"
)

Go to your model, create a new measure, and paste the DAX code above, replacing 'YourTable' with the actual name of your table.

Click on the visual you want to format.

In the Visualizations pane, select the field to format, click the drop-down, and select Conditional formatting.

Select either Background color or Font color.

In the conditional formatting window, choose Field value and select the measure you just created.

Specify the colors for "Red", "Green", and "Yellow" as needed.

Now your visual will reflect the color based on the categorization defined in the measure! Let me know if you need more help!

[v-yohua-msft]

Hi, @dhrupal_shah 

Based on your information, I create a sample table:

Then create a calculated column, try the following dax:

ColorCategory = 
SWITCH(
    TRUE(),
    [Region] = "Central" && [Value] >= 1 && [Value] <= 10000, "Red",
    [Region] = "Central" && [Value] > 10000 && [Value] <= 15000, "Green",
    [Region] = "Central" && [Value] > 15000, "Blue",
    [Region] = "East" && [Value] >= 1 && [Value] <= 2000, "Red",
    [Region] = "East" && [Value] > 2000 && [Value] <= 5000, "Yellow",
    [Region] = "East" && [Value] > 5000, "Green",
    "No Color"
)

Create a table visual and put Region and Value in it. Click Format pane and expend cell elements, you can choose Background color or Font color.

Select Field value, and then select the calculated column you just created

Here is my preview:

You can learn more from the following link:

Apply conditional table formatting in Power BI - Power BI | Microsoft Learn

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

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

[dhrupal_shah]

Thanks for your replay but i have below condition.

Product Central   East    West  South     North

Prod1       1000    200    5000    3000     10000

Prod2       2000    500    3000    5000       8000

[Vinay_eshwara]

Hi, dhrupal_shah
To achieve conditional formatting for your table you need to create separate measures for each color such as,

Central Color =

SWITCH(

    TRUE(),

    'Table (2)'[Central] >= 1 && 'Table (2)'[Central] <= 10000, "#FF0000", // Red for Central, 1 - 10000

    'Table (2)'[Central] > 10000 && 'Table (2)'[Central] <= 15000, "#008000", // Green for Central, 10000 - 15000

    "#FFFFFF" // Default to white if none of the conditions are met

)

East Color =

SWITCH(

    TRUE(),

    'Table (2)'[East] >= 1 && 'Table (2)'[East] <= 2000, "#FF0000", // Red for East, 1 - 2000

    "#FFFFFF" // Default to white if none of the conditions are met

)

West Color =

SWITCH(

    TRUE(),

    'Table (2)'[West] >= 1 && 'Table (2)'[West] <= 3000, "#FF0000", // Red for West, 1 - 3000

    'Table (2)'[West] > 3000 && 'Table (2)'[West] <= 5000, "#008000", // Green for West, 3000 - 5000

    "#FFFFFF" // Default to white if none of the conditions are met

)

North Color =

SWITCH(

    TRUE(),

    'Table (2)'[North] >= 1 && 'Table (2)'[North] <= 3000, "#FF0000", // Red for North, 1 - 3000

    'Table (2)'[North] > 3000 && 'Table (2)'[North] <= 10000, "#008000", // Green for North, 3000 - 5000

    "#FFFFFF" // Default to white if none of the conditions are met

)

After creating separate measures you can apply conditional formatting for each column by selecting Background color in cond formatting and  secting field value and selecting respective color measure.
Here is a screenshot for reference.

 After applying conditional formatting for each column the result looks like below image

If any new columns or new rows are added just update the measures and it works.

If you find my solution helpful please give a kudos.
Thanks,
Vinay.

Preview

[dhrupal_shah]

  

Thanks vinay, your reply near to my solution , please help me to solve it. please refer my 

below data and screen shots.

Product	Zone	Amount
Prod1	Central	5000
Prod2	Central	2000
Prod3	Central	6000
Prod4	Central	5000
Prod5	Central	4000
Prod1	East	500
Prod2	East	200
Prod3	East	1000
Prod4	East	300
Prod5	East	200
Prod1	North	15000
Prod2	North	10000
Prod3	North	4000
Prod4	North	20000
Prod5	North	5000
Prod1	South	8000
Prod2	South	9000
Prod3	South	7000
Prod4	South	8000
Prod5	South	8000
Prod1	West	25000
Prod2	West	10000
Prod3	West	25000
Prod4	West	20000
Prod5	West	20000

[Vinay_eshwara · Accepted solution]

I can see that your data is in long format and you have three columns Product, Zone and Amount. And you used Matrix visual. 
Now you can directly create a new calculated column because calculated columns evaluate row by row . In your case, the calculated column can evaluate the Zone and Amount values for each row directly.
You can use the below DAX for new column

ZoneColor =

SWITCH(

    TRUE(),

    'Table'[Zone] = "Central" && 'Table'[Amount] >= 1 && 'Table'[Amount] <= 4000, "#FF0000", // Red for Central, 1 - 4000

    'Table'[Zone] = "Central" && 'Table'[Amount] > 4000 && 'Table'[Amount] <= 10000, "#008000", // Green for Central, 4000 - 10000

    'Table'[Zone] = "East" && 'Table'[Amount] >= 1 && 'Table'[Amount] <= 500, "#FF0000", // Red for East, 1 - 500

    'Table'[Zone] = "East" && 'Table'[Amount] > 500, "#008000", // Green for East, > 500

    'Table'[Zone] = "North" && 'Table'[Amount] >= 1 && 'Table'[Amount] <= 10000, "#FF0000", // Red for North, 1 - 10000

    'Table'[Zone] = "North" && 'Table'[Amount] > 10000, "#008000", // Green for North, > 10000

    'Table'[Zone] = "South" && 'Table'[Amount] >= 1 && 'Table'[Amount] <= 5000, "#FF0000", // Red for South, 1 - 5000

    'Table'[Zone] = "South" && 'Table'[Amount] > 5000, "#008000", // Green for South, > 5000

    'Table'[Zone] = "West" && 'Table'[Amount] >= 1 && 'Table'[Amount] <= 20000, "#FF0000", // Red for West, 1 - 20000

    'Table'[Zone] = "West" && 'Table'[Amount] > 20000, "#008000", // Green for West, > 20000

    "#FFFFFF" // Default color (white) if no condition is met

)

To create a new column click on three dots of the table in the Data pane. You can customize colors as you wish in the DAX query.
Then apply conditional formatting for Amount under the Values dropdown in the visualizations pane. 
Choose Field value and newly created column in conditional formatting as shown below.


Then the result looks like this

Please mark this as solution and give kudos if it met your goal.
Thanks,
Vinay.

[dhrupal_shah]

Dear vinay,

same condition with date not work.

Production_Color =
SWITCH(
TRUE(),
TRIM('Production'[ComponentName]) = "CTL 1" && 'Production'[QtyRecv] > 50 , "#008000", // Green
TRIM('Production'[ComponentName]) = "CTL 1" && 'Production'[QtyRecv] >=35 && 'Production'[QtyRecv] <= 50, "#000a00", // Black
TRIM('Production'[ComponentName]) = "CTL 1" && 'Production'[QtyRecv] > 0 && 'Production'[QtyRecv] < 35, "#FF0000" //Red
)

thanks in advance.

Dhrupal

this data from sql query.

[dhrupal_shah]

thanks vinay , i get solution as provided by you.

[Kedar_Pande]

@dhrupal_shah 

Create a Measure for Conditional Formatting

ConditionalColor = 
SWITCH(
TRUE(),
SELECTEDVALUE('YourTable'[Region]) = "Central" && 'YourTable'[Value] >= 1 && 'YourTable'[Value] <= 10000, "Red",
SELECTEDVALUE('YourTable'[Region]) = "Central" && 'YourTable'[Value] > 10000 && 'YourTable'[Value] <= 15000, "Green",
SELECTEDVALUE('YourTable'[Region]) = "East" && 'YourTable'[Value] >= 1 && 'YourTable'[Value] <= 2000, "Red",
SELECTEDVALUE('YourTable'[Region]) = "East" && 'YourTable'[Value] > 2000 && 'YourTable'[Value] <= 5000, "Yellow",
SELECTEDVALUE('YourTable'[Region]) = "East" && 'YourTable'[Value] > 5000, "Green",
"No Color" // Fallback
)

Select the Visual, Go to Format Pane, Click on the fx button next to Conditional formatting, Choose your ConditionalColor measure in the Based on field dropdown

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[dhrupal_shah]

Thanks for your reply , i have below condition 

Product   South  East   West  North

Prod 1       5000   200  9000  11000

Prod 2       2000   900  3000    1000

Prod 3       1000       0    500  15000   

[Angith_Nair]

Hi @dhrupal_shah 

To apply conditional formatting based on specific ranges for different states in Power BI, you can use a DAX measure that defines the color based on your rules. Then, you can apply conditional formatting based on this measure.

Step 1: Create a Measure for Color Formatting
Go to Modeling > New Measure and create a measure to define the color logic:

FormatColor = 
SWITCH(
    TRUE(),
    'Table'[State] = "Central" && 'Table'[Value] >= 1 && 'Table'[Value] <= 10000, "#FF0000", // Red for Central, 1 - 10000
    'Table'[State] = "Central" && 'Table'[Value] > 10000 && 'Table'[Value] <= 15000, "#008000", // Green for Central, 10000 - 15000
    'Table'[State] = "East" && 'Table'[Value] >= 1 && 'Table'[Value] <= 2000, "#FF0000", // Red for East, 1 - 2000
    'Table'[State] = "East" && 'Table'[Value] > 2000 && 'Table'[Value] <= 5000, "#FFFF00", // Yellow for East, 2000 - 5000
    'Table'[State] = "East" && 'Table'[Value] > 5000, "#008000", // Green for East, > 5000
    "#FFFFFF" // Default to white if none of the conditions are met
)

Step 2: Apply Conditional Formatting in Power BI
> Select your table or matrix visual.
> Go to the Format your visual pane, expand Conditional formatting, and choose Background color or Font color based on preference.
> Under Format by, select Field value.
> In the Based on field dropdown, select the FormatColor measure.

If new states with different ranges are added, you can update the FormatColor measure by adding additional conditions.

[dhrupal_shah]

Thanks for your reply , i have below condition 

Product   South  East   West  North

Prod 1       5000   200  9000  11000

Prod 2       2000   900  3000    1000

Prod 3       1000       0    500  15000   

[_AAndrade]

Hi @dhrupal_shah,

Take a look at this post: https://community.fabric.microsoft.com/t5/Desktop/Conditional-formatting/m-p/4238800

You could do this using Rules or using a Switch function as you can see in the article.

[dhrupal_shah]

Thanks for your reply , i have below condition 

Product   South  East   West  North

Prod 1       5000   200  9000  11000

Prod 2       2000   900  3000    1000

Prod 3       1000       0    500  15000