cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Post Partisan

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

Thanks and Regards,

5 REPLIES 5
Solution Sage

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

Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696
Post Partisan

Hi @AmiraBedh ,

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

Any suggestion?

Thanks and Regards,

Solution Sage

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

Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696
Post Partisan

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.

Thanks and Regards,

Solution Sage

Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors