Forum Discussion

ADSL's avatar
ADSL
Post Prodigy
3 years ago

Time Tracking with time group

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,
 
 

5 Replies

  • 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's avatar
      ADSL
      Post Prodigy

      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's avatar
        AmiraBedh
        Super User

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