Forum Discussion

zzzsou's avatar
zzzsou
Frequent Visitor
2 years ago

Work hour distribution heat map

I have tried sereral times but ended up coming here to look for some help.


Here are my requirements and data

Purpose: I would like to see distrubution of working hours on table (hour *  week day) to see which day or time line are busier than others

 

'data_table' 

 

each row has 3 essential attributes (Operator, start_date_hour, finish_date_hour) and I added columns for start_weekday and start_hour.

 

'hour_table'

'day_table' 

and I created two more tables (day_table, hour_table) in order to see all values in x,y axis

 

 

 

Expectation

 

As you can see above,

Operator A started work (weekday : 7, hour : 22 pm, duration 8.5 hours) so finished work (weekday :1  hour : 6:30 am)

the table shows 1 hour at (7,22),(7,23),(1,0),(1,1),(1,2),(1,3),(1,4),(1,5) and 0.5 hour at (1,6)

Operator B started work (weekday : 6, hour : 08 am, duration 12 hours) so finished work (weekday :6  hour : 20 pm)

the table shows 1 hour at (6,8)~(6,19)

Operator C started work (weekday : 1, hour : 4 am, duration 12 hours) so finished work (weekday : 1  hour : 10 am)

the table shows 1 hour at (1,4)~(1,9)

 

Could you kindly help me make dax measures for this working hour distribution event?