Conditional Formatting for a matrix based on another table

[aasamassa]

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.

[Syndicate_Admin]

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 answer

Conditional 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!

[aasamassa]

Hello @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

[lbendlin]

Not enough data. Where is the "actual value"  column to compare to the target?

[Syndicate_Admin · Accepted solution]

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!

[aasamassa]

It worked with your solution thank you!

[lbendlin]

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[KNP]

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.