Scenario:
Suppose we have a dataset where the Metric_Value column contains DateTime values for each Metric_Name, as shown below.
From this dataset, we need to extract the time component from Metric_Value and convert it into a numeric format so it can be used to display properly on a clustered column chart.
The refined Dataset with Time Values is as follows:
We will use the below measure to format the TimeAsNumber to Time (HH:mm:ss) to display on the Chart for the Data Labels.
let
SampleData =
#table(
{"Metric_Id", "Metric_Name", "Metric_Value", "Metric_DataType"},
{
{1, "JobRunDateTime", #datetime(2023, 5, 10, 14, 30, 00), "DateTime"},
{2, "JobRunDateTime", #datetime(2024, 1, 15, 21, 45, 30), "DateTime"},
{3, "JobStartTime", #time(9, 45, 15), "Time"},
{4, "JobStartTime", #time(14, 30, 10), "Time"}
}
),
// Convert Time to duration:
_Duration = Table.AddColumn(
SampleData,
"TimeAsDuration",
each #duration(
0,
Time.Hour([Metric_Value]),
Time.Minute([Metric_Value]),
Time.Second([Metric_Value])
),
type duration
),
// Convert duration to total seconds (numeric):
_TotalSeconds = Table.AddColumn(
_Duration,
"TimeAsSeconds",
each Duration.TotalSeconds(
#duration(
0,
Time.Hour([Metric_Value]),
Time.Minute([Metric_Value]),
Time.Second([Metric_Value])
)
),
type number
),
// Convert Time into Hour.MinuteSeconds numeric format with padded Seconds:
_NumericTime = Table.AddColumn(
_TotalSeconds,
"TimeAsNumber",
each Number.Round(
Number.FromText(
Text.From(Time.Hour([Metric_Value])) & "." &
Text.PadStart(Text.From(Time.Minute([Metric_Value])),2,"0") &
Text.PadStart(Text.From(Time.Second([Metric_Value])),2,"0")
),
4),
type number
)
in
_NumericTime
The final formatted dataset includes a TimeAsNumber column, which represents the numeric conversion of time values. We will use this column to generate a clustered column chart that displays the maximum time value for each metric.
The TimeAsDuration (d.HH:mm:ss) and TimeAsSeconds columns were created for reference purposes only and are not used further in this example.
Format TimeLabel =
VAR TimeValue = MAX(tbl_Metrics_TimeValues[TimeAsNumber]) -- E.g. 9.4515
VAR Hrs = INT(TimeValue) -- Hours
VAR Mnts_Sec = ROUND((TimeValue- Hrs) * 10000,0) -- Minutes+Seconds
VAR Mnts = INT(DIVIDE(Mnts_Sec,100)) -- Minutes
VAR Sec = MOD(Mnts,100) -- Seconds
RETURN
FORMAT(Hrs,"0") & ":" & FORMAT(Mnts,"00") & ":" & FORMAT(Sec,"00")
Generate the Clustered Column Chart:
We will use the Metric Name on the X-Axis and TimeAsNumber column on Y-axis to generate the Clustered Column chart with Max value for each Metric.
We will use the Metric Name on the X-Axis and TimeAsNumber column on Y-axis to generate the Clustered Column chart with Max value for each Metric.
Next, in the Data Labels > Values section of the chart formatting options, assign the measure Format TimeLabel Value (created earlier) so that the actual metric Time values are displayed on columns instead of the number.
We can use the below measure to format the Columns of the Chart.
Metric ColorFormat =
SWITCH(
SELECTEDVALUE(tbl_Metrics_TimeValues[Metric_Name]),
"JobRunDateTime", "#1E90FF", -- Azure Blue
"JobStartTime", "#4682B4", -- SteelBlue
"#808080" -- Gray (default color)
)
Result:
The final output is a clustered column chart with Metric_Name on the X‑axis and metric Max time values shown as data labels.
--------------------------------------------------------------------------------------------------------
Thanks
--------------------------------------------------------------------------------------------------------
