cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
brett_walton Regular Visitor
Regular Visitor

Determine Shift Based on time range

I have a table of transactions that post entries at specific time (I have a fields that captures the time and location) I want to add a column that identifies shift it was posted based on  shift table sample below). I am adding a column because the visiuals I create will include all data from all locations so slicers won't help. Any idea the best way ? Thanks 

 

Loc    Shift no       Start                End 

1          1              05:00:00        14:00:00   

1          2              14:00:00        21:00:00  

2          1              04:00:00        13:00:00   

2          2              13:00:00        20:00:00  

3          1              04:30:00        13:30:00   

3          2              13:30:00        20:30:00  

1 ACCEPTED SOLUTION

Accepted Solutions
BhaveshPatel Super Contributor
Super Contributor

Re: Determine Shift Based on time range

For the Solution I have created two tables,

Dimension table has all the master information of location, shift no, start and end times.

Fact Table has all the transactional information abouth the location and Persons and Start time. Please see the attached screenshots.

 

Shift ranges1.PNGRelationshipsShift Ranges.PNGCreate calculated column using DAXshift ranges2.PNGfilter Blanksshift ranges 4.PNGFinal output

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

View solution in original post

3 REPLIES 3
BhaveshPatel Super Contributor
Super Contributor

Re: Determine Shift Based on time range

Hi Brett,

 

You can use merge queries option in the Query editor with left outer join. To recreate a solution for you, please post the full data model and sample tables with expected output.

 

 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
brett_walton Regular Visitor
Regular Visitor

Re: Determine Shift Based on time range

Thanks for the reply ... I have a table that list production and its time and I want to determine shift based on the define shifts in the table in my original post

 

so for the table below and determine the first one is shift 1 (because its between the start and end times for the shifts in the shift table) .. the second one would be shift 2 , Make sense ?

 

Loc 1 10 pcs 05:20:00 (want to add a shift column that says "1")

Loc 2 50 pcs 14:43:50 (want to add a shift column that says "2")

BhaveshPatel Super Contributor
Super Contributor

Re: Determine Shift Based on time range

For the Solution I have created two tables,

Dimension table has all the master information of location, shift no, start and end times.

Fact Table has all the transactional information abouth the location and Persons and Start time. Please see the attached screenshots.

 

Shift ranges1.PNGRelationshipsShift Ranges.PNGCreate calculated column using DAXshift ranges2.PNGfilter Blanksshift ranges 4.PNGFinal output

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)