Power BI is displaying time values as text "HH:mm:ss" text instead of the actual value

[Anonymous]

I'm having a strange issue in Power BI Desktop.

I have data in my source (Azure Analysis Services) where I'm calculating the average time for chat answering and formatted as "HH:mm:ss" (last column in screenshot below).

Power BI is now displaying "HH:mm:ss" instead of the actual value.

Below is the same table in Excel and the average time is being displayed correctly.

I can also add that field in a graph over a few days and the graph is changing so Power BI is still getting some values, it just doesn't display them.

[Anonymous]

Hi @Anonymous,

I can't reproduce this on my side, 'HH:mm:ss' parameter works well on my side. Can you please share a pbix file for test?

Regards,

Xiaoxin Sheng

[Anonymous]

Hi Xiaoxin,

The data is coming from a corporate Azure Data warehouse that I can't give you access to. So sending you the pbix isn't going to be any help.

The source data field is called Answered time and it is simply raw duration (in seconds) / 86400 and then formatted as time field.

Power BI has no problem displaying it correctly formatted as data element.

Average Speed of Answer is a measure that is simply AVERAGE([Answered Time]), again formatted as time.

I have tried adding a new measure within Power BI that is using the same formula. Again it works when displaying the result as decimal but isn't working when I format the result as time in Power BI (last two columns).

I also tried the same with an unformatted field in the data source but the behavior in Power BI is the same, displaying decimals iw fine but time format is only showing the format string and not the value.

[Anonymous · Accepted solution]

Just in case someone else comes across this issue. I think this might be a case where Excel simply is more lenient whereas Power BI has higher expectations on the incoming data.

I updated my formulas in the data warehouse from AVERAGE(x) to TIMEVALUE(FORMAT(AVERAGE(x),"HH:mm:ss")) which now displayes the times correctly in Power BI and Excel still is fine too.

I'm just still puzzled that Power BI would display the text "HH:mm:ss"...