Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi, I'm trying to create a chart like below:
The data is similar to:
EQUIP | START TIME | FINISH TIME | CATEGOY COLOR |
HT001 | 1/01/2020 09:00:02 | 1/01/2020 09:45:00 | UNSCHEDULED |
HT003 | 1/01/2020 09:50:01 | 1/01/2020 14:58:12 | BACKLOG |
HT004 | 1/01/2020 12:50:23 | 1/01/2020 14:50:00 | UNSCHEDULED |
HT002 | 1/01/2020 17:18:01 | 2/01/2020 04:00:09 | SCHEDULED |
how I can solve this?, Thank you in advance for any advice!
Solved! Go to Solution.
Hi @Julver ,
First you need to create a calendar table with date time something simlar to this:
DateTime =
ADDCOLUMNS (
CROSSJOIN (
CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2020, 12, 31 ) ),
UNION (
ROW ( "Time", TIME ( 1, 0, 0 ) ),
ROW ( "Time", TIME ( 2, 0, 0 ) ),
ROW ( "Time", TIME ( 3, 0, 0 ) ),
ROW ( "Time", TIME ( 4, 0, 0 ) ),
ROW ( "Time", TIME ( 5, 0, 0 ) ),
ROW ( "Time", TIME ( 6, 0, 0 ) ),
ROW ( "Time", TIME ( 7, 0, 0 ) ),
ROW ( "Time", TIME ( 9, 0, 0 ) ),
ROW ( "Time", TIME ( 10, 0, 0 ) ),
ROW ( "Time", TIME ( 11, 0, 0 ) ),
ROW ( "Time", TIME ( 12, 0, 0 ) ),
ROW ( "Time", TIME ( 13, 0, 0 ) ),
ROW ( "Time", TIME ( 14, 0, 0 ) ),
ROW ( "Time", TIME ( 15, 0, 0 ) ),
ROW ( "Time", TIME ( 16, 0, 0 ) ),
ROW ( "Time", TIME ( 17, 0, 0 ) ),
ROW ( "Time", TIME ( 18, 0, 0 ) ),
ROW ( "Time", TIME ( 19, 0, 0 ) ),
ROW ( "Time", TIME ( 20, 0, 0 ) ),
ROW ( "Time", TIME ( 21, 0, 0 ) ),
ROW ( "Time", TIME ( 22, 0, 0 ) ),
ROW ( "Time", TIME ( 23, 0, 0 ) ),
ROW ( "Time", TIME ( 24, 0, 0 ) )
)
),
"DateTime", [Date] + [Time],
"Hour", HOUR ( [Time] )
)
Now create the following measure:
Count based on hours =
CALCULATE (
COUNT ( 'Table'[EQUIP] ),
FILTER (
'Table',
'Table'[FINISH TIME] >= MAX ( DateTime[DateTime] )
&& 'Table'[START TIME] <= MAX ( DateTime[DateTime] )
)
)
This gives the result below using the Date time has an axis and the measure in values:
Looking at your picture seems to me that you are not considering the seconds in each of the start and finish so I created two new columns:
FinishTimeNoSeconds = MROUND ( 'Table'[FINISH TIME], TIME ( 1, 0, 0 ) ) + TIME ( 0, 0, 0 )
StartTimeNoSeconds = MROUND ( 'Table'[START TIME], TIME ( 1, 0, 0 ) ) + TIME ( 0, 0, 0 )
This removes the seconds from the start and finish now you just need to redo your measure to:
Count based on hours no seconds =
CALCULATE (
COUNT ( 'Table'[EQUIP] ),
FILTER (
'Table',
'Table'[FinishTimeNoSeconds] >= MAX ( DateTime[DateTime] )
&& 'Table'[StartTimeNoSeconds] <= MAX ( DateTime[DateTime] )
)
)
Result below:
For the date and hours you need to turn of the concactenate values and show values without data.
Result in attach PBIX file.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Portuguêsthanks so much @MFelix , I had 2 results:
A.
B.
Is it possible to show in graph B, fill the same color as the legend of graph A?
Hi @Julver when you say to have the same colours what do you mean to have the colours of the no programado and programado based on equipo?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsPlease, how I can configurate to start the report at 7:00 am and finish 07:00 an from the next day?
exactly, colours of the no programado and programado based on equipo.
Hi @Julver ,
First you need to create a calendar table with date time something simlar to this:
DateTime =
ADDCOLUMNS (
CROSSJOIN (
CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2020, 12, 31 ) ),
UNION (
ROW ( "Time", TIME ( 1, 0, 0 ) ),
ROW ( "Time", TIME ( 2, 0, 0 ) ),
ROW ( "Time", TIME ( 3, 0, 0 ) ),
ROW ( "Time", TIME ( 4, 0, 0 ) ),
ROW ( "Time", TIME ( 5, 0, 0 ) ),
ROW ( "Time", TIME ( 6, 0, 0 ) ),
ROW ( "Time", TIME ( 7, 0, 0 ) ),
ROW ( "Time", TIME ( 9, 0, 0 ) ),
ROW ( "Time", TIME ( 10, 0, 0 ) ),
ROW ( "Time", TIME ( 11, 0, 0 ) ),
ROW ( "Time", TIME ( 12, 0, 0 ) ),
ROW ( "Time", TIME ( 13, 0, 0 ) ),
ROW ( "Time", TIME ( 14, 0, 0 ) ),
ROW ( "Time", TIME ( 15, 0, 0 ) ),
ROW ( "Time", TIME ( 16, 0, 0 ) ),
ROW ( "Time", TIME ( 17, 0, 0 ) ),
ROW ( "Time", TIME ( 18, 0, 0 ) ),
ROW ( "Time", TIME ( 19, 0, 0 ) ),
ROW ( "Time", TIME ( 20, 0, 0 ) ),
ROW ( "Time", TIME ( 21, 0, 0 ) ),
ROW ( "Time", TIME ( 22, 0, 0 ) ),
ROW ( "Time", TIME ( 23, 0, 0 ) ),
ROW ( "Time", TIME ( 24, 0, 0 ) )
)
),
"DateTime", [Date] + [Time],
"Hour", HOUR ( [Time] )
)
Now create the following measure:
Count based on hours =
CALCULATE (
COUNT ( 'Table'[EQUIP] ),
FILTER (
'Table',
'Table'[FINISH TIME] >= MAX ( DateTime[DateTime] )
&& 'Table'[START TIME] <= MAX ( DateTime[DateTime] )
)
)
This gives the result below using the Date time has an axis and the measure in values:
Looking at your picture seems to me that you are not considering the seconds in each of the start and finish so I created two new columns:
FinishTimeNoSeconds = MROUND ( 'Table'[FINISH TIME], TIME ( 1, 0, 0 ) ) + TIME ( 0, 0, 0 )
StartTimeNoSeconds = MROUND ( 'Table'[START TIME], TIME ( 1, 0, 0 ) ) + TIME ( 0, 0, 0 )
This removes the seconds from the start and finish now you just need to redo your measure to:
Count based on hours no seconds =
CALCULATE (
COUNT ( 'Table'[EQUIP] ),
FILTER (
'Table',
'Table'[FinishTimeNoSeconds] >= MAX ( DateTime[DateTime] )
&& 'Table'[StartTimeNoSeconds] <= MAX ( DateTime[DateTime] )
)
)
Result below:
For the date and hours you need to turn of the concactenate values and show values without data.
Result in attach PBIX file.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |