Forum Discussion
Help Converting Excel Meal Compliance Formula to DAX
I'm trying to recreate an excel formula for meal compliance in DAX and I'm getting stuck. My original formula is
=IFS(AND(O2>=6.01,K2=K3),"Late Meal",AND(O2>=6.01,K2<>K3),"No Meal",O2<6.01,"(left blank)")
Where O = Regular Hrs worked, and
K is a helper column in Excel that concatenates two other columns, EID and Date.
Meal breaks are to be taken between 4th and 6th hr of each shift.
IF regular hrs are greater than 6.01 AND the employee has two time-entries for the day, THEN they had a late lunch
IF regular hrs are greater than 6.01 AND the employee has a single time entry for the day, THEN they missed lunch
It looks like my best bet would be us a switch function similar to the one below, but with meal compliance logic. I'm stuck in creating a column with a running total of the # of time punches each day for each employee. Ideally the first punch of the day would be 1, the second punch a 2, and so on. Thank you for your help!
2 Replies
- PhilipTreacy
Super User
Hi Anonymous
Any chance you can share your PBIX file? Too hard to recreate everything.
Regards
Phil
Of course. The PBIX File is here. Thanks for your help!
