Forum Discussion

EnrichedUser's avatar
EnrichedUser
Helper III
5 years ago

Reference Virtual Table - Value Between Two Times

Hi All, 

 

I have a virtual table

_Table = GENERATE(
SUMMARIZECOLUMNS(ActivitySheet[StartTime2], ActivitySheet[EndTime2], ActivitySheet[UserID], ActivitySheet[TransactionType], ActivitySheet[BranchID], ActivitySheet[ActivitySheetDate]),
        FILTER(VALUES(TimeTable[Time]),
            TimeTable[Time] >= ActivitySheet[StartTime2] && TimeTable[Time] <= ActivitySheet[EndTime2])
            )

 

The virtual table provides all the times between my StartTime2 and EndTime2 Values from my ActivitySheet table using the TimeTable. 

 

I need to create a messure using the virtual table to show when each person worked. 

 

Current:

 

Expected Output:

 

[ActivitySheet] - Sample

ActivitySheetDateUserIDStartTime2EndTime2TransactionType
4/26/2021IGARCIA7:53:00 AM8:41:00 AMORD.PICK
4/26/2021IGARCIA8:41:00 AM8:41:00 AMOTHER
4/26/2021IGARCIA8:41:00 AM8:42:00 AMOTHER
4/26/2021IGARCIA8:42:00 AM10:50:00 AMORD.PICK
4/26/2021IGARCIA10:50:00 AM1:18:00 PMWHSE.PICK
4/26/2021IGARCIA1:18:00 PM2:58:00 PMORD.PICK
4/26/2021IGARCIA2:58:00 PM3:01:00 PMORD.PICK

 

[TimeTable] - Sample

TimeTable is in a relationship with [ActivitySheet] on 'TimeTable'[Time] One to 'ActivitySheet'[Startime2]' Many

TimeHourMinuteHour NumberNext HourPeriodSortTimeKey10Min
8:04:00 AM8:00:00 AM489:00:00 AM28040
8:05:00 AM8:00:00 AM589:00:00 AM28050
8:06:00 AM8:00:00 AM689:00:00 AM28060
8:07:00 AM8:00:00 AM789:00:00 AM28070
8:08:00 AM8:00:00 AM889:00:00 AM28080
8:09:00 AM8:00:00 AM989:00:00 AM28090
8:10:00 AM8:00:00 AM1089:00:00 AM281010
8:11:00 AM8:00:00 AM1189:00:00 AM281110

 

 

 

Need Help

3 Replies

  • EnrichedUser's avatar
    EnrichedUser
    Helper III
    5 years ago

    I was able to get it working using the below messure and making the table a calculated table instead of a virtual one

    Working Segment = 
IF(COUNT('Test 2'[Time])>0,
1,
BLANK()
)

     

    It nearly doubled the size of my file and is not a great long term solution. 

    • EnrichedUser's avatar
      EnrichedUser
      Helper III
      5 years ago

      I think you always post the same thing and maybe it sometimes helps people, but your response doesnt relate to anything in this request and is too generic to be useful. 

       

      and yes ive read that before.