I have a list of teacher_ids and all of their payroll data, previously joined with data on certification programs they have completed. The (program) column is redundant and inconsistent with pay period date (date). The program column was assigned to every row of payroll data.
The rate change column was previously calculated to flag a change in salary between pay periods.
With all that, my end analysis goal is to come up with a count of teacher_ids that completed the program AND also saw a rate change >10%. If they completed the program but did not see a rate change, I want to label that count of teachers as well. Also... a third label being teachers that did NOT complete the program.
That's all to say that I have no idea how to go about this. I essentially need to group by teacher id and evaluate two columns (rate change and program).. but I need to evaluate it row by row since the sum of rate change by teacher id won't work.. my measure needs to look at rate change by row, by teacher id.
Any DAX, or logic input would be helpful. Thank you all!
Sample data:
| teacher_ids | payroll date | rate change | program |
| 1 | 1-Jan | 0% | completed |
| 1 | 1-Feb | 10% | completed |
| 1 | 1-Mar | 0% | completed |
| 2 | 1-Jan | 0% | completed |
| 2 | 1-Feb | 0% | completed |
| 2 | 1-Mar | 0% | completed |
| 3 | 1-Jan | 10% | null |
| 3 | 1-Feb | 0% | null |
| 3 | 1-Mar | 0% | null |
