Calculated Column To Determine Attendance Status

[imbetto]

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"

)

[v-hashadapu]

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.

[v-hashadapu]

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.

[v-hashadapu]

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.

[rohit1991 · Accepted solution]

Hi  @imbetto

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

[lbendlin]

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...