Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a measure that I am hoping to return in HH:MM;SS format instead of decimal.
The source value is currently in HH:MM;SS format
AVG DISP = CALCULATE(AVERAGE(rlmain[Time]),FILTER( rlmain, FIND( "DISP",rlmain[tencode],, 0) <> 0 ))
Solved! Go to Solution.
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.
Excellent That worked as needed!!!
but I was getting a syntax error using ; and instead used ,
Great.
To , and ; - it depends what locale your system uses. Mine PowerBI/Excel/... expects semicollon.
An issue is created: https://community.powerbi.com/t5/Issues/DAX-TIME-function-raises-an-exception-if-the-second-part-is-...
Btw. the max value of seconds you can currently use is 32 767 - I wrote a wrong value in my last post 😞
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |