Forum Discussion
Conditional formatting matrix values based on other table column values
I have a matrix of employee hours as values, dates as columns and names as rows. I need to conditional format all the days for those employee where:
1) Date is more than hired date of employee and less than released date of employee
2) Date is a weekday
3) Entered hours is less than required daily hours.
I have an employee table which has employee name, required daily hours, hire date and release date. the transactional table which has all the enter hours for employees is joined with employee table. My main issue is getting the hire date, release date and daily required hours from employee table to compare with hours present in transactional table. I tried creating a flag to use for conditional formatting:
The problem with above dax is that instead of bringing date hired and released for each employee it is
I got the answer. Thanks all for reading this. Here is what I did :
I created measure for selected date hired, selected date released for the employee in the context.
SelectedStaff = SELECTEDVALUE(table[table.employee]) to capture the staff in context
Selected Date Hired = CALCULATE(MIN(Employee[Employee.dateHired]),FILTER(Employee,Employee[Employee.displayName] = [SelectedStaff])) to capture the hired date of employee in context
Similar dax for date release. Finally created the outstanding flag with below formula:
Outstanding flag = IF([WeekDay- Weekend] = "Weekday" && [Selected Date] >= [Selected Date Hired] && [Selected Date] <= MAX(TODAY(),[Selected Date Released]) && [Available Hours new] >= [Selected Daily Hours] ,1,0)
Applied the conditional formatting through outstanding flag on the entered hours by rule. It worked
3 Replies
Anonymous , in this case, employee needs to there in visual (means in context), can you share what all you plan to have in the output table.
amitchandak As I mentioned names are in rows. Names are nothing but employee names.
I got the answer. Thanks all for reading this. Here is what I did :
I created measure for selected date hired, selected date released for the employee in the context.
SelectedStaff = SELECTEDVALUE(table[table.employee]) to capture the staff in context
Selected Date Hired = CALCULATE(MIN(Employee[Employee.dateHired]),FILTER(Employee,Employee[Employee.displayName] = [SelectedStaff])) to capture the hired date of employee in context
Similar dax for date release. Finally created the outstanding flag with below formula:
Outstanding flag = IF([WeekDay- Weekend] = "Weekday" && [Selected Date] >= [Selected Date Hired] && [Selected Date] <= MAX(TODAY(),[Selected Date Released]) && [Available Hours new] >= [Selected Daily Hours] ,1,0)
Applied the conditional formatting through outstanding flag on the entered hours by rule. It worked
