Forum Discussion

EnrichedUser's avatar
EnrichedUser
Icon for Helper III rankHelper III
5 years ago

24 Hour Gannt Chart Matrix

Hi All, 

 

I am looking to make a visual the show when each person did a certain task. 

 

I have two tables. The first is [ActivitySheet]

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

 

The secondtable is [TimeTable].

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

 

Current:

 

Expected Output:

 

Notes:

The idea is that if the StartTime2 >= TimeTable[Time] && EndTime2 <= TimeTable[Time] then place a 1 for that transaction. 

 

 The 10Min column is defined by

10Min = FLOOR(TimeTable[Minute],10)

 

 

Need Help

7 Replies

    • EnrichedUser's avatar
      EnrichedUser
      Icon for Helper III rankHelper III
      5 years ago

      v-jingzhang That is correct, there will likely be overlap between time and task. 

       

      I also took a look at the link but I havent been able to make much progress. 

    • EnrichedUser's avatar
      EnrichedUser
      Icon for Helper III rankHelper III
      5 years ago

      v-jingzhang Thanks for taking a look. I went through your file and saw the working time column you added in power query and see how it works. I dont think it will work well for my application since that activity sheet has upwards on 1mm rows as is. 

      • v-jingzhang's avatar
        v-jingzhang
        Icon for Community Support rankCommunity Support
        5 years ago

        EnrichedUser I know it's not perfect as it brings too much data into the table and adds a lot of rows. There may be some measure could deal with it. I will try to work it out but I'm not sure if I'm able to. 

  • EnrichedUser's avatar
    EnrichedUser
    Icon for Helper III rankHelper III
    5 years ago

    I think maybe there is a way to make a virtual table using crossjoin to add all the times from [TimeTable] to the [activitysheet] where the TimeTable Value is between the start and end time. 

  • EnrichedUser's avatar
    EnrichedUser
    Icon for Helper III rankHelper III
    5 years ago
        GENERATE(
        ActivitySheet,
        FILTER(TimeTable,
            TimeTable[Time] >= ActivitySheet[StartTime2] && 
            TimeTable[Time] <= ActivitySheet[EndTime2]))