Forum Discussion
Average Time with Filter / Measure
Hi bcampbell,
that's a little bit tricky. If you have a computed column, it is simple to change the data type and format of a column. But because a measure is not a part of a table, you can't do the same. But, you can use the function TIME(hour; minute; second) for transforming your AVERAGE value. The average value is a part of day.
The simple solution were:
CalculateAvgAsTime = TIME(0; 0; [CalculateAvgFrom] * 24 * 3600)
But it doesn't work, because Time expects max 32565 as value!!! Don't ask me why when a day has 86400 seconds. But I will create an issue for it.
My workaround is following:
CalculateAvgAsTime = TIME ( [CalculateAvgFrom] * 24; 0; MOD ( [CalculateAvgFrom] * 24 * 3600; 3600 ) )
I use hours, ignore minutes and after that compute seconds in the hour with help of modulo.
Hi bcampbell,
that's a little bit tricky. If you have a computed column, it is simple to change the data type and format of a column. But because a measure is not a part of a table, you can't do the same. But, you can use the function TIME(hour; minute; second) for transforming your AVERAGE value. The average value is a part of day.
The simple solution were:
CalculateAvgAsTime = TIME(0; 0; [CalculateAvgFrom] * 24 * 3600)
But it doesn't work, because Time expects max 32565 as value!!! Don't ask me why when a day has 86400 seconds. But I will create an issue for it.
My workaround is following:
CalculateAvgAsTime = TIME ( [CalculateAvgFrom] * 24; 0; MOD ( [CalculateAvgFrom] * 24 * 3600; 3600 ) )
I use hours, ignore minutes and after that compute seconds in the hour with help of modulo.
An issue is created: https://community.powerbi.com/t5/Issues/DAX-TIME-function-raises-an-exception-if-the-second-part-is-gt/idi-p/632185#M39516
Btw. the max value of seconds you can currently use is 32 767 - I wrote a wrong value in my last post :(