Time graph in hours, minutes and seconds

[Golfdos]

I’ve an Excel with 5 rows. The results of a running race are the times when arriving at different points of the race in hour, minute and second format. Here's an example:

Name	Time 1	Time 2	Time 3	Time 4
James	0:20:00	0:35:00	0:40:00	0:58:00
John	0:15:00	0:25:00	0:36:00	0:47:00

In another tab I’ve a list of hours, minutes and seconds, all the possible ones from the second 0 to 1 hour.

In power bi I need to make a line graph where the X is all the time and the Y is each of the times of each of the participants of the race. How do I have to do it?

[Anonymous]

Hi rajendraongole1 ,thanks for the quick reply, I'll add more.

Hi @Golfdos ,

In Power BI, timestamp comparisons at the second level are not supported. So I think I should change the data type to "Text".

1.Use the following DAX expression to create columns

Time_1 = FORMAT([Time 1],"hh:nn:ss")

Time_2 = FORMAT([Time 2],"hh:nn:ss")

2.Use the following DAX expression to create a table

Time = ADDCOLUMNS(GENERATESERIES(1,3600,1),"Time",FORMAT(TIME(0,0,[Value]),"hh:nn:ss"))

3. Use the following DAX expression to create a measure

Measure = 
VAR _time = SELECTEDVALUE('Time'[Time])
VAR _result = COUNTROWS(FILTER('Table',[Time_1] = _time || [Time_2] = _time || [Time_3] = _time || [Time_4] = _time))
RETURN 
_result

4.Final output

Best Regards,
Wenbin Zhou

[rajendraongole1]

Hi @Golfdos - Generate a time table in Power BI using DAX. This table should include all possible times from 0:00:00 to 1:00:00 in 1-second intervals:

TimeTable =

ADDCOLUMNS(

    GENERATESERIES(0, 3600, 1), -- 3600 seconds (1 hour)

    "Time", TIME(0, 0, [Value]) -- Convert seconds to time format

)

Now create a relationship between the TimeTable[Time] column and the unpivoted Value column from your race data. This ensures that each race time can map to the seconds in the time table.

In the report view, create a Line Chart visualization.
Set:
X-Axis: TimeTable[Time].
Y-Axis: Use a calculated measure that aggregates the participants' times.