Determine Shift Based on time range

[brett_walton]

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  

[BhaveshPatel]

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.

[brett_walton]

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 · Accepted solution]

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.

RelationshipsCreate calculated column using DAXfilter BlanksFinal output