Forum Discussion
Solved
AverageX with Calculated Column and Format function
I created a calculated column to get a time duration value using the Format function, and it looks great, but I can't use AverageX on it because now it's a string value. Is there a way to get this fo...
Hello stvn43,
You can use the TIME function in DAX to create a duration value instead of using the FORMAT function.
Difference = VAR EditTime = IF([editbegintime] <= 0, TIME(0, 0, 0), TimeToEdit[editendtime] - TimeToEdit[editbegintime]) RETURN EditTime Time to Caption = VAR TTC = AVERAGEX(FILTER(TimeToEdit, [service] = "Captioning"), [Difference]) RETURN FORMAT(TTC, "HH:MM:SS")By using the TIME function, you can now use the AverageX function on the Difference column because it returns a duration value instead of a string.
I hope this helps! Let me know if you have any further questions.
Many thanks, Sahir!
Hello stvn43,
You can use the TIME function in DAX to create a duration value instead of using the FORMAT function.
Difference =
VAR EditTime =
IF([editbegintime] <= 0, TIME(0, 0, 0),
TimeToEdit[editendtime] - TimeToEdit[editbegintime])
RETURN EditTime
Time to Caption =
VAR TTC = AVERAGEX(FILTER(TimeToEdit, [service] = "Captioning"), [Difference])
RETURN FORMAT(TTC, "HH:MM:SS")
By using the TIME function, you can now use the AverageX function on the Difference column because it returns a duration value instead of a string.
I hope this helps! Let me know if you have any further questions.