24 Hour Gannt Chart Matrix

[EnrichedUser]

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]

ActivitySheetDate	UserID	StartTime2	EndTime2	TransactionType
4/26/2021	IGARCIA	7:53:00 AM	8:41:00 AM	ORD.PICK
4/26/2021	IGARCIA	8:41:00 AM	8:41:00 AM	OTHER
4/26/2021	IGARCIA	8:41:00 AM	8:42:00 AM	OTHER
4/26/2021	IGARCIA	8:42:00 AM	10:50:00 AM	ORD.PICK
4/26/2021	IGARCIA	10:50:00 AM	1:18:00 PM	WHSE.PICK
4/26/2021	IGARCIA	1:18:00 PM	2:58:00 PM	ORD.PICK
4/26/2021	IGARCIA	2:58:00 PM	3:01:00 PM	ORD.PICK

The secondtable is [TimeTable].

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

Time	Hour	Minute	Hour Number	Next Hour	PeriodSort	TimeKey	10Min
8:04:00 AM	8:00:00 AM	4	8	9:00:00 AM	2	804	0
8:05:00 AM	8:00:00 AM	5	8	9:00:00 AM	2	805	0
8:06:00 AM	8:00:00 AM	6	8	9:00:00 AM	2	806	0
8:07:00 AM	8:00:00 AM	7	8	9:00:00 AM	2	807	0
8:08:00 AM	8:00:00 AM	8	8	9:00:00 AM	2	808	0
8:09:00 AM	8:00:00 AM	9	8	9:00:00 AM	2	809	0
8:10:00 AM	8:00:00 AM	10	8	9:00:00 AM	2	810	10
8:11:00 AM	8:00:00 AM	11	8	9:00:00 AM	2	811	10

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)

[EnrichedUser]

    GENERATE(
        ActivitySheet,
        FILTER(TimeTable,
            TimeTable[Time] >= ActivitySheet[StartTime2] && 
            TimeTable[Time] <= ActivitySheet[EndTime2]))

[EnrichedUser]

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. 

[v-jingzhang]

Hi @EnrichedUser 

I made some transformations to the table in Power Query Editor. You could see specific steps in the attached pbix as well as the measures.

Hope this helps.

Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[EnrichedUser]

@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]

@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. 

[v-jingzhang]

Hi @EnrichedUser 

It seems your request is similar to the Gantt Chart in this blog (Think inside the box - Custom visuals using Standard Visuals) but more complicated. I haven't worked out how to write the correct measure that works. According to your sample data, a person may work for the same task at multiple time periods in a day, right? 

Regards,
Community Support Team _ Jing

[EnrichedUser]

@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.