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 ,

You're very close with your SWITCH(TRUE()) logic, but from the behavior you're seeing — especially that it's only returning "Absent" and "Not Required" — it looks like the issue might be in the evaluation order of your conditions and possibly how ISBLANK() interacts with [Completed_Hours].

In particular, your conditions that check ISBLANK([Completed_Hours]) are placed first, which could be prematurely catching rows that actually do have values — but Power BI may not be treating 0 or small decimals properly, or the field may be calculated in a way that it's not blank even when the value is technically empty.

Attendance Status =
SWITCH(
    TRUE(),
    
    // Not Required (Weekends or Holidays with no hours)
    ISBLANK([Completed_Hours]) && RELATED(Calendar[DayType]) IN {"Weekend", "Holiday"}, "Not Required",

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

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

    // Absent if blank but it's a working day
    ISBLANK([Completed_Hours]) && RELATED(Calendar[DayType]) = "Regular", "Absent",
    ISBLANK([Completed_Hours]) && RELATED(Calendar[DayType]) = "Ramadan", "Absent",

    // Fallback
    "Unknown"
)

Passionate about leveraging data analytics to drive strategic decision-making and foster business growth.

Connect with me on LinkedIn: Rohit Kumar

[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...