Conditional Colour Formatting using 2 different columns in the condition

[Deevo_]

Hi All,

Thanks in advance.

What I am trying to achieve:

Conditional formatting a cell based on 2 conditions (The value in the cell i want to colour and also in another column).

What I currently have:

• Measures:
  • Total Leave  = SUM(Leave taken on 1st June 2023)
  • Total Timesheet hours = SUM(Timesheet hours submitted for 1st June 2023)
• Then i created 2 checking measures:
  • 1st Check = IF([Total Leave] > 0, 1, 0)
  • 2nd Check = IF([Total Timesheet hours] > 0, 1, 0)
• Then i created a final checking measure:
  • Final Check = IF(([1st Check] + [2nd Check]) > 1, 1,0)
• Then i applied the conditional formatting using "Values" and this is almost what I need, but I found a problem. The final check is not checking row by row for each individual "Staff name" in the list, instead it appears to do the final check by the entire list Grand Total and then applies the colours to the Grand total values instead of each individual staff.

Report Table:

Staff Name	Leave taken on 1st June 2023	Timesheet hours submitted for 1st June 2023
Alice	1	0
Bob	0	40
Cameron	1	40

Expected Results:

• IF "Leave taken on 1st June 2023" and "Timesheet hours submitted for 1st June 2023" are both populated (or the sum of both measures is greater than 1, not sure which method to use here), then Colour both cells Salmon Pink, otherwise do not colour.
• Using the table data: For Staff Name = 'Cameron', the cells "Leave taken on 1st June 2023" and "Timesheet hours submitted for 1st June 2023" would both be coloured in a Salmon pink. All other staff will not have any colour.

I hope my explanation is clear.

Thank you

[lbendlin · Accepted solution]

You may want to have a word with your Accessibilty experts.

See attached.

[Deevo_]

Hi lbenlin, Thank you for your reply. This definitely works as intended in my table! thanks so much. I have now change this table into a matrix and added another 2 rows on top to group it by the Location and Team the staff are working in.

Looks like this now (Please see comments):

Staff Name		Leave taken on 1st June 2023	Timesheet hours submitted for 1st June 2023	Comments
Location1	Team 1			False Positive Issue: When collapsed this Location1 row is coloured pink
	Alice	1	0
	Alex	0	40
Location2	Team 2			This appears to be correct when collapsed because there is one Staff (Barb) that meets the conditonal criteria.
	Bob	0	40
	Barb	1	40
Location3	Team 3			This appears to be correct when collapsed because there is one Staff (Cameron) that meets the conditonal criteria.
	Cameron	1	40
	Callum	0	40

Follow on Question:

1. Once I add 'Location' and 'Team Names' to the matrix (Each team has more than one person in them), I can see that your measure is applying the colour formatting to the row total when I fully collapse the rows. When I fully expand the matrix, inside the Teams that were coloured (when collapsed), there are no staff in that Team who have both cells populated. It appears to be applying the formula at which ever level is open. Can this be fixed?

[lbendlin]

Read about ISINSCOPE and adjust the measure accordingly. You may also need to use SUMMARIZE if you want to filter up.

[Deevo_]

Great Thank you for your advice! Have a great day!