Conditional font colour formatting based on predefined values from another table

[Nivedhana]

We are working on a BI with multiple tables. There are 2 separate tables as seen below. The predefined values are stored in one table and actual values stored in another. We are using actual values in the BI as a matrix table. We want to highlight the font in red only if the actual parameter is not in range of predefined value. PLease advise how we can achieve the same.

Predefined				Actual values
Product	Parameters	Value1	value2				Product	Date	Parameters	Value
TV	a	1	4				TV	05-02-2020	a	3
	b	5	9						b	4
	c	15	20						c	25
Mobile	a	5	10				Mobile	20-02-2020	a	3
	b	4	9						b	4
	c	15	20						c	25
Laptop	a	3	7				Laptop	05-02-2020	a	3
	b	5	9						b	4
	c	18	25						c	25

[Nivedhana]

Hi,

Thanks! Can you please explain the data modelling part? Now both the tables are connected only by product details. There is no connection between the parameters

[123abc · Accepted solution]

If there is no direct relationship or connection between the two tables based on parameters, you can still achieve conditional font color formatting by using a DAX (Data Analysis Expressions) measure that calculates whether the actual values are within the predefined ranges for each product and parameter combination. Here's how you can modify the approach:

1. Create a DAX measure in your "Actual values" table that checks if the actual value for a specific product and parameter falls within the predefined range. You can use a measure like this:

IsValueWithinRange =
VAR CurrentProduct = 'Actual values'[Product]
VAR CurrentParameter = 'Actual values'[Parameters]
VAR ActualValue = 'Actual values'[Value]
VAR PredefinedValue1 = CALCULATE(MAX('Predefined'[Value1]),
FILTER('Predefined', 'Predefined'[Product] = CurrentProduct && 'Predefined'[Parameters] = CurrentParameter))
VAR PredefinedValue2 = CALCULATE(MAX('Predefined'[Value2]),
FILTER('Predefined', 'Predefined'[Product] = CurrentProduct && 'Predefined'[Parameters] = CurrentParameter))
RETURN
IF(ActualValue >= PredefinedValue1 && ActualValue <= PredefinedValue2, "Green", "Red")

This measure calculates whether the actual value is within the predefined range for the current product and parameter combination. It returns "Green" if it is within the range and "Red" if it's not.

1. Once you've created the DAX measure, you can use it in your matrix table to conditionally format the font color. Here's how you can do this:

   • Select the cell or text where you want to apply the font color formatting.
   • Go to the "Conditional formatting" option in your BI tool.
   • Choose "Font color."
   • Set up the formatting rules like this:
     • If the "IsValueWithinRange" measure equals "Red," set the font color to red.
     • If the "IsValueWithinRange" measure equals "Green," set the font color to green.

2. Repeat steps 1 and 2 for each cell or text where you want to apply the conditional font color formatting in your matrix table.

This approach uses a DAX measure to dynamically calculate whether the actual values are within the predefined ranges for each product and parameter combination. It doesn't rely on a direct relationship between the two tables based on parameters but instead leverages DAX calculations to determine the font color based on the data in both tables.

[Nivedhana]

Hi @123abc ,

In additional to the above condition, for some fileds, the condition is taking from another field in parameter. For ex, for parameter B for TV, the red highlight is based on parameter D value of TV and which check if D is within Predefined values  

[Nivedhana]

Hi @123abc ,

Did you get a chance to check this? After implemeting the above case, we found out that for a few parameters, the colour code is based on other parameters. For example, for Mobile parameter A is coloured red if value of parameter B is not in range of predefined value B. I tried by myself to do this but unable to do it. Could you please assist?

[Nivedhana]

Thank you very much! It worked!! 

[123abc]

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

[123abc]

To highlight the font in red for actual values that are not in the range of predefined values in Power BI, you can use conditional formatting. Here's how you can achieve this:

1. Data Modeling: Ensure that both the "Predefined" table and the "Actual Values" table are loaded into Power BI, and there's a relationship between them. Specifically, make sure that there's a relationship between the "Product" and "Parameters" columns in both tables.

2. Create a Measure for Conditional Formatting: Create a measure that compares the actual value with the predefined range and returns a value that you can use for conditional formatting. Here's an example measure that checks if the actual value is not within the predefined range:

NotInRange =
IF(
NOT(ISFILTERED('Actual Values'[Value])) || -- Check if Value is selected
ISBLANK(SELECTEDVALUE('Actual Values'[Value])), -- Check if Value is blank
BLANK(), -- Return blank if not selected or blank
IF(
SELECTEDVALUE('Actual Values'[Value]) >= MIN('Predefined'[Value1]) &&
SELECTEDVALUE('Actual Values'[Value]) <= MAX('Predefined'[Value2]),
BLANK(), -- Within the range, return blank for no formatting
1 -- Outside the range, return 1 for formatting
)
)

1. This measure checks if the selected value is within the predefined range and returns 1 if it's not.

2. Apply Conditional Formatting: Now, apply conditional formatting to the matrix or table visual in your report. Follow these steps:

   • Select the actual value column in your visual.
   • In the "Visualizations" pane, expand "Conditional formatting."
   • Choose "Font color."
   • Select "Field value" and choose the "NotInRange" measure you created.
   • Set the font color to red for the "1" value.

3. Test the Report: Now, when you view your report, the font color for actual values that are not within the predefined range will be red.

This approach uses DAX measures to calculate whether a value is within the predefined range and then applies conditional formatting based on the measure's result. Ensure that you have a proper data model and relationships set up between your tables for this to work effectively.