Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Deevo_
Resolver I
Resolver I

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.