Forum Discussion
Conditional Formatting for a matrix based on another table
Hello,
I have a matrix that shows the number of employees audited per production lines per month.
I would like to add a conditional format that shows if the target of audits was met for each produtcion line (green if target was met, red if not met). As every line has a different target, I have made another table that shows the target for each line. (for example, Ligne 2 has a target of 11 so for June the number should be in red as it has done only 7 audits)
How can I make the conditional formating using the data from the target table?
I tried a measure but it did not work.
Hello
I hope to see the question understood. You have 2 tables and want to reference the second table (Objectives) as conditional formatting in your parent table. I hope this helps you, in any case if not, tell me how it could help you oh you like it if that's what you asked.
Conditional formatting, using the second table as ref.:
Solution:
1. Create a table with the following formula:
Calendar =
ADDCOLUMNS (
CALENDAR (DATE(2025, 1, 1), DATE(2025, 12, 31)),
"MesAño", FORMAT([Date], "MMM-YYYY"),
"MonthYearOrder", YEAR([Date]) * 100 + MONTH([Date])
)
2. Relate the 2 tables you mention to the new Calendar table:
- PK, Calendar (Date) and Audit (Date)
- PK, Audit (LineProduction) and AuditObjective = (MonthlyObjective)
3. Create a Matrix table with the following formulas:
- In the Rows section, select = LineaProduction (Audit table)
- In the Column, Select the Calendar = MonthYear
- Under Values, select = Total Audit
- Formula for Total Audits = COUNTROWS
4. Conditional Formatting:
Select the matrix table and in the Values section "Total Audits" = Use the Background color option and select MeetObjective.
I show you the values you will put in:
This is the formula you'll need to create first for MeetObjective.
(This formula looks up the values in the second table to give it conditional formatting)
MeetObjective =
VAR line = SELECTEDVALUE(Audits[Production Line])
VAR objective = LOOKUPVALUE(
ObjectivesAudit[MonthlyObjective],
ObjectivesAudit[Production Line],
line
)
VAR Audits = [Total Audits]
RETURN
IF(Audits >= objective, 1, 0)
________________________________________________________
Data model of the first table: Audits
Data model of the second table: ObjectivesAudit
Luck!
8 Replies
Hi aasamassa,
We need to see your data model showing the relationships between the tables.
It's difficult to answer without having that info because it'll change the way the measure is written.
Sudo code:
__cf_target_met = SWITCH( TRUE() , [result] < [target], "red" , [result] >= [target], "green" )This goes in the Cell formatting for background colour.
Hello
I hope to see the question understood. You have 2 tables and want to reference the second table (Objectives) as conditional formatting in your parent table. I hope this helps you, in any case if not, tell me how it could help you oh you like it if that's what you asked.
Conditional formatting, using the second table as ref.:
Solution:
1. Create a table with the following formula:
Calendar =
ADDCOLUMNS (
CALENDAR (DATE(2025, 1, 1), DATE(2025, 12, 31)),
"MesAño", FORMAT([Date], "MMM-YYYY"),
"MonthYearOrder", YEAR([Date]) * 100 + MONTH([Date])
)
2. Relate the 2 tables you mention to the new Calendar table:
- PK, Calendar (Date) and Audit (Date)
- PK, Audit (LineProduction) and AuditObjective = (MonthlyObjective)
3. Create a Matrix table with the following formulas:
- In the Rows section, select = LineaProduction (Audit table)
- In the Column, Select the Calendar = MonthYear
- Under Values, select = Total Audit
- Formula for Total Audits = COUNTROWS
4. Conditional Formatting:
Select the matrix table and in the Values section "Total Audits" = Use the Background color option and select MeetObjective.
I show you the values you will put in:
This is the formula you'll need to create first for MeetObjective.
(This formula looks up the values in the second table to give it conditional formatting)
MeetObjective =
VAR line = SELECTEDVALUE(Audits[Production Line])
VAR objective = LOOKUPVALUE(
ObjectivesAudit[MonthlyObjective],
ObjectivesAudit[Production Line],
line
)
VAR Audits = [Total Audits]
RETURN
IF(Audits >= objective, 1, 0)
________________________________________________________
Data model of the first table: Audits
Data model of the second table: ObjectivesAudit
Luck!
It worked with your solution thank you!
It's good that it worked for you!
If you like, you can like the answer (the thumb icon)
That will help others know that the solution is useful, and it also supports me as a collaborator in the
community.Thank you
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Hello KNP lbendlin Syndicate_Admin ,
So here is the model. I blurred the other tables because they are for other visuals.
Here is a sample of the data used in the table 'Toolbox 5S Audit':
Department/Zone Employee Name Date of Audit Ligne 3 Name xyz 2025-06-23 Ligne 2 Name abc 2025-06-22 Ligne 5 Name lmn 2025-06-21 I use a count of the employee name to know how many audits were done in the month
Here is a sample of the data used in the table 'Audit Targets'
Ligne Target Ligne 2 10 Ligne 3 15 Not enough data. Where is the "actual value" column to compare to the target?
I hope I have understood your question correctly. You mention that you have two tables, and you want to use the second one as a reference for applying conditional formatting. Here are the steps to achieve it. If your case is different, please tell me and I will gladly help you adjust it.
And if this solved your doubt, I would love for you to "like" the answerConditional formatting with reference to the second table as objectives
Solution:
1. Create a table with the following formula:
Calendar =
ADDCOLUMNS (
CALENDAR (DATE(2025, 1, 1), DATE(2025, 12, 31)),
"MesAño", FORMAT([Date], "MMM-YYYY"),
"MonthYearOrder", YEAR([Date]) * 100 + MONTH([Date]))
2. Relate the 2 tables you mention to the new Calendar table:
- PK, Calendar (Date) and Audit (Date)
- PK, Audit (LineaPrioduccion) and ObjectiveAudit (LineProduction)
3. Crea a Matrix table with the following formulas:
- In the Rows= Production Line section (Audits)
- In the Column, Select the Calendar = MonthYear (New Calendar)
- Under Values, select = Total Audits (New Formula)
- Create this formula, Total Audits = COUNTROWS(Audits)
4. Conditional Formatting:
Select the matrix table and in the Values= section, use the Backgroung option and select MeetTarget. I show you what values you will put on yourself
-> Create this formula first
MeetObjective =
VAR line = SELECTEDVALUE(Audits[Production Line])
VAR objective = LOOKUPVALUE(
ObjectivesAudit[MonthlyObjective],
ObjectivesAudit[Production Line],
line
)
VAR Audits = [Total Audits]
RETURN
IF(Audits >= objective, 1, 0)
___________
Table Template1 Audit
Table Model2 ObjectiveAudit
Luck!
