cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Deevo_
Helper II
Helper II

Conditional Colour Formatting using 2 different columns in the condition

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 NameLeave taken on 1st June 2023Timesheet hours submitted for 1st June 2023
Alice10
Bob040
Cameron140

 

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

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

lbendlin_0-1686361822045.png

 

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

 

See attached.

View solution in original post

4 REPLIES 4
lbendlin
Super User
Super User

lbendlin_0-1686361822045.png

 

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

 

See attached.

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 2023Timesheet hours submitted for 1st June 2023Comments
Location1Team 1  False Positive Issue: When collapsed this Location1 row is coloured pink
 Alice10 
 Alex040 
Location2Team 2  This appears to be correct when collapsed because there is one Staff (Barb) that meets the conditonal criteria.
 Bob040 
 Barb140 
Location3Team 3  This appears to be correct when collapsed because there is one Staff (Cameron) that meets the conditonal criteria.
 Cameron140 
 Callum040 

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?

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

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

Helpful resources

Announcements
Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors