When did/why did MS get so bad?

[james_pbi]

So, my business has spent thousands on Power BI and is still no better than using Excel. I stupidly thought it would be easy to find solutions to issues, like it was with Excel. Wrong!

I've spent half a day trying to get Power BI not to reset to zero, and go above 24 for hh:mm:ss - in Excel we'd format it as [hh]:mm:ss. Why do we not have that functionality in Power BI?

I finally found some posts on here explaining what to do - I coulsn't get them to work, so posted a detailed reply. Guess what - after submitting, it wanted me to sign in and promptly deleted my post. Another hour messing about with authenticator to actually sign-in, but getting differing messages about continuing registering, sign-in-ing, etc. 

For God's sake! Why is it so difficult!

After being a MS evangelist for years, hate to say, but the turn to Power BI is turning me off.

[lbendlin]

No, I did not mean hh:mm:ss. I suggested you use the Format function to convert numbers to text, which would allow you to have an hour count greater than 24. You can still combine that with mm:ss if you want.

[james_pbi]

Sorry, getting confused with where your replies posted. Sorry, with you not specifying Format as TEXT I assumed you meant format as hh:mm:ss as that is what the results actually is hours, minutes and seconds.

FORMAT() as text? How do you do that then? I can't see anything on docs.microsoftc.com or dax.guide.

Converting to text within the data itself does not allow the result to go over 24 hours, nor does it allow you to summerise.

[lbendlin]

"if I change the data type to hh:mm:ss"

Don't do that - use the FORMAT() function to get your data into the right display format, but keep your column type as is.

[james_pbi]

Nope that doesn't work either. Only certain columns will accept a format change; you didn't specify what format, but I'm guessing you meant hh:mm:ss?

When it does format to hh:mm:ss, again, when it gets to 24hrs, it resets to zero.

(and again, it won't summarise.)

[v-lionel-msft]

Hi @james_pbi ,

Wouldn't it work if you did it like this?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[james_pbi]

Unfortunately not. It when the result goes past 24 hours, it just cycles back round. So 25 hours would be output as 01:00:00.

[lbendlin]

You may want to use a Duration field type, not a Time field type.

As for the forum logout/loss of entered post - that is indeed a very annoying issue in this forum, however not really related to Power BI.

[james_pbi]

Unfortunately, that doesn't work either.

In Power Query, if I change type to Duration, when I close & apply, the result in the table is a decimal. In any visualisation, this will sum ok.

In the table, if I change the data type to hh:mm:ss: a) I am no longer able to summarise the results, b) Again, the results do not extend past 24.