Time Tracking with time group

[ADSL]

Hi BI Community Team,

As per expectation result, we want to know the earliest of check-in time and latest of check-out time by time group as the screenshot below:

So, we have tired the new column with measure below.

Time_Group2 = SWITCH(TRUE(),

[Check-In] >= TIME(1,0,0) && [Check-Out] < TIME(8,0,0), "Early Morning",

[Check-In] >= TIME(8,0,0) && [Check-Out] < TIME(12,0,0), "Morning",

[Check-In] >= TIME(12,0,0) && [Check-Out] < TIME(13,30,0), "Lunch",

[Check-In] >= TIME(13,30,0) && [Check-Out] < TIME(17,30,0), "Afternoon",

[Check-In] >= TIME(17,30,0) && [Check-Out] < TIME(21,0,0), "Evening", "Late Evening")

then we found that time group is showing wrong if value of check-out is blank. normally, value of check-out will be the same as check-in if it's blank.

Any suggestion/advise?

Thanks and Regards,

[AmiraBedh]

I adjusted your dax code to the following : 

Time_Group2 = 


SWITCH(
    TRUE(),
    ISBLANK([Check-Out]), SWITCH(
        TRUE(),
        [Check-In] >= TIME(1, 0, 0) && [Check-In] < TIME(8, 0, 0), "Early Morning",
        [Check-In] >= TIME(8, 0, 0) && [Check-In] < TIME(12, 0, 0), "Morning",
        [Check-In] >= TIME(12, 0, 0) && [Check-In] < TIME(13, 30, 0), "Lunch",
        [Check-In] >= TIME(13, 30, 0) && [Check-In] < TIME(17, 30, 0), "Afternoon",
        [Check-In] >= TIME(17, 30, 0) && [Check-In] < TIME(21, 0, 0), "Evening",
        "Late Evening"
    ),
    [Check-In] >= TIME(1, 0, 0) && [Check-Out] < TIME(8, 0, 0), "Early Morning",
    [Check-In] >= TIME(8, 0, 0) && [Check-Out] < TIME(12, 0, 0), "Morning",
    [Check-In] >= TIME(12, 0, 0) && [Check-Out] < TIME(13, 30, 0), "Lunch",
    [Check-In] >= TIME(13, 30, 0) && [Check-Out] < TIME(17, 30, 0), "Afternoon",
    [Check-In] >= TIME(17, 30, 0) && [Check-Out] < TIME(21, 0, 0), "Evening",
    "Late Evening"
)

The code you provided doesn't handle the case where the "Check-Out" value is blank. In your provided logic, you directly compare "Check-In" and "Check-Out" times without considering the scenario when "Check-Out" is blank.

Since the code does not include any conditional check to handle a blank "Check-Out" value, it may lead to incorrect results in that specific case. If "Check-Out" is blank, the code does not have any instruction on how to handle that scenario, so it may lead to wrong categorizations or unexpected results.

[ADSL]

Hi @AmiraBedh ,

After trying and check then I see some times assigned to wrong time group as the screenshot.

Any suggestion?

Thanks and Regards,

[AmiraBedh]

Is it related to Time_Group2 or another calculated columns? Can you please be more clear in your explanation ?

[ADSL]

Hi Amira,

It's related to Time_Group2. If you check the screenshot below and yellow highlight, you will see the time of check-in & check-out. It should be in Lunch or afternoon in Time_Group2.

But it's showed "Late Evening" that it's not correct of time group.

Please kindly advise and check.

Thanks and Regards,

[AmiraBedh]

Can you please provide your logic case by case ?