cancel
Showing results for
Did you mean:
Regular Visitor

## DAX for identifying shifts

I have a timestamp label column (mm/dd/yyyy h:mm:ss) that I need to use to identify what shift it took place. I am connected to a server and cannot adjust the timestamp format or create columns. I need to create a measure to label the shifts. The shifts are
1a:  7:00am - 10:20am
2a: 10:20am - 1:30pm
3a:  1:30pm - 4:50pm
4a:  4:50pm - 7:00pm
1b:  7:00pm - 10:20pm
2b: 10:20pm - 1:30am
3b:  1:30am - 4:50am
4b:  4:50am - 7:00am

I've seen similar questions asked here, but they all seem to be for adding a column and not a measure. Thanks in advance.

2 REPLIES 2
Solution Sage

You can either use a disconnected table or a SWITCH(TRUE() measure like the one below to do that. For vDT, use your expression to get the datetime value from your column.

``````Shift =
VAR vDT =
MAX ( T2[DT] )
VAR vThisTime =
TIME ( HOUR ( vDT ), MINUTE ( vDT ), 0 )
VAR vResult =
SWITCH (
TRUE (),
vThisTime >= TIME ( 7, 0, 0 )
&& vThisTime < TIME ( 10, 20, 0 ), "1a",
vThisTime >= TIME ( 10, 20, 0 )
&& vThisTime < TIME ( 13, 30, 0 ), "2a",
vThisTime >= TIME ( 13, 30, 0 )
&& vThisTime < TIME ( 16, 50, 0 ), "3a",
vThisTime >= TIME ( 16, 50, 0 )
&& vThisTime < TIME ( 19, 00, 0 ), "4a",
vThisTime >= TIME ( 19, 0, 0 )
&& vThisTime < TIME ( 22, 20, 0 ), "1b",
vThisTime >= TIME ( 22, 20, 0 )
&& vThisTime < TIME ( 1, 30, 0 ), "2b",
vThisTime >= TIME ( 1, 30, 0 )
&& vThisTime <= TIME ( 4, 50, 0 ), "3b",
"4b"
)
RETURN
vResult``````

Pat

Microsoft Employee
Regular Visitor

Thank you! Here's what I ended up doing -

First, I created a measure to pull the time out of the date/time column.

Time Open EST = MINX('DwellBay collection', 'DwellBay collection'[DELETE Time Open EST])

Then used

Period =
SWITCH(
TRUE(),
TIMEVALUE([Time Open EST]) >= TIME(7,00,0) && TIMEVALUE([Time Open EST]) < TIME(10,20,0),"1a",
TIMEVALUE([Time Open EST]) >= TIME(10,20,0) && TIMEVALUE([Time Open EST]) < TIME(14,00,0),"2a",
TIMEVALUE([Time Open EST]) >= TIME(14,00,0) && TIMEVALUE([Time Open EST]) < TIME(16,50,0),"3a",
TIMEVALUE([Time Open EST]) >= TIME(16,50,0) && TIMEVALUE([Time Open EST]) < TIME(19,00,0),"4a",
TIMEVALUE([Time Open EST]) >= TIME(19,00,0) && TIMEVALUE([Time Open EST]) < TIME(22,20,0),"1b",
TIMEVALUE([Time Open EST]) >= TIME(22,20,0) && TIMEVALUE([Time Open EST]) < TIME(23,59,59),"2b",
TIMEVALUE([Time Open EST]) >= TIME(0,00,0) && TIMEVALUE([Time Open EST]) < TIME(2,00,0),"2b",
TIMEVALUE([Time Open EST]) >= TIME(2,00,0) && TIMEVALUE([Time Open EST]) < TIME(4,50,0),"3b",
TIMEVALUE([Time Open EST]) >= TIME(4,50,0) && TIMEVALUE([Time Open EST]) < TIME(7,00,0),"4b"
)

Probably overly complicated but looks like it's working appropriately. I am having some issues when adding other values to the matrix but I believe that's a table relationship issue.

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors