topic Re: Calculated Column To Determine Attendance Status in DAX Commands and Tips

Calculated Column To Determine Attendance Status

Anonymous — Sun, 23 Mar 2025 09:10:20 GMT

I am building an attendance dashboard and already integrated holidays and weekends and a calculated column that calculated the durantion between the earliest entry and latest exit timestamps and everything checks out except for the "Attendance Status" calculated column where I am using the SWITCH function with all possible conditions but I am not getting the expected results as can be seen in the screenshot, when the day is "Regular" and the completed hours are equal or more than 8 then it should be fulfilled otherwise it is incomplete same with ramadan but the hours being 5 instead but I "Absent" and "Not Required" are the only results I am getting even when that is not the case. I tried to test out each condition individualy to troubleshoot but to no avail.

Below is the dax expression I used:

Attendance Status =

SWITCH(

TRUE(),

ISBLANK([Completed_Hours]) && RELATED(Calendar[DayType]) IN {"Weekend", "Holiday"}, "Not Required",

ISBLANK([Completed_Hours]) && RELATED(Calendar[DayType]) = "Regular", "Absent",

ISBLANK([Completed_Hours]) && RELATED(Calendar[DayType]) = "Ramadan", "Absent",

RELATED(Calendar[DayType]) = "Regular" && [Completed_Hours] >= 8, "Fulfilled",

RELATED(Calendar[DayType]) = "Regular" && [Completed_Hours] < 8, "Incomplete",

RELATED(Calendar[DayType]) = "Ramadan" && [Completed_Hours] >= 5, "Fulfilled",

RELATED(Calendar[DayType]) = "Ramadan" && [Completed_Hours] < 5, "Incomplete",

RELATED(Calendar[DayType]) IN {"Weekend", "Holiday"} && [Completed_Hours] >= 8, "Fulfilled",

RELATED(Calendar[DayType]) IN {"Weekend", "Holiday"} && [Completed_Hours] < 8, "Incomplete",

"Unknown"

)

Re: Calculated Column To Determine Attendance Status

lbendlin — Sun, 23 Mar 2025 12:53:29 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Calculated Column To Determine Attendance Status

rohit1991 — Mon, 28 Jul 2025 13:35:16 GMT

Hi Anonymous

What I can see, the issue might be that your SWITCH(TRUE()) logic is evaluating the hours before checking the special day types like Holiday, Weekend, etc. So even if it should be marked as “Holiday” or “Leave,” it’s defaulting to “Absent” too early.

One approach that’s worked for me in the past is to check all the special conditions first (like holidays, weekends, approved leave, etc.), and only then check the hours worked.

Here’s a cleaner structure you could try:

Attendance Status = VAR Hrs = COALESCE('Attendance'[Completed Hours], 0) VAR IsHoliday = RELATED('Calendar'[HolidayFlag]) = 1 VAR IsWeekend = RELATED('Calendar'[WeekendFlag]) = 1 VAR NotReq = RELATED('Calendar'[NotRequiredFlag]) = 1 VAR IsLeave = RELATED('Leave'[ApprovedLeaveFlag]) = 1 VAR IsSick = RELATED('Sickness'[SickFlag]) = 1 RETURN SWITCH ( TRUE(), NotReq, "Not Required", IsHoliday, "Holiday", IsWeekend, "Weekend", IsLeave, "Leave", IsSick, "Sick", ISBLANK('Attendance'[Completed Hours]), "No Time", Hrs >= 7.5, "Present", Hrs >= 4, "Half Day", Hrs > 0, "Partial", "Absent" )

This structure checks all special cases first and only then evaluates based on hours. It also handles blanks more safely using COALESCE.

One thing to double-check make sure the relationships to your Calendar and other lookup tables are active and working as expected

Re: Calculated Column To Determine Attendance Status

v-hashadapu — Sat, 29 Mar 2025 10:08:17 GMT

Hi Anonymous , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

Re: Calculated Column To Determine Attendance Status

v-hashadapu — Mon, 07 Apr 2025 09:54:42 GMT

Hi @imbetto , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

Re: Calculated Column To Determine Attendance Status

v-hashadapu — Thu, 10 Apr 2025 11:33:44 GMT