topic Re: Problems witha time format measure in DAX Commands and Tips

Problems witha time format measure

Borjaveiga — Fri, 09 Feb 2024 18:47:31 GMT

Good afternoon to all,

Maybe somene could help me out with this problem I am finding.

I created a measure to show time as such 1:02:00 using this DAX

Hours = FORMAT(time(0,sum('Descargas fichajes'[registered_time_mins]),0),"hh:nn:ss")

the column is a hole number and when I use it in a visual I get this error message

It's in sapnish but basicaly says that either one part of the TIME function has an error or the result is too big or too small
it makes no sense since we are talking about 150hours by row if I diplay it in a table with the employee Id as first column in the visual object

Thanks in advanced

Re: Problems witha time format measure

lbendlin — Sat, 10 Feb 2024 01:40:06 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Problems witha time format measure

Borjaveiga — Sat, 10 Feb 2024 07:03:21 GMT

the column that the meassure refers to is registered_time_mins and looks likes this:

registered_time_mins	expected_time	expected_time_mins
9120	168h	10080
10260	168h	10080
10731	168h	10080
7500	160h	9600
7680	168h	10080
9600	160h	9600

Re: Problems witha time format measure

Dangar332 — Sat, 10 Feb 2024 07:47:27 GMT

hi, Borjaveiga

replace your sum with min like below
just adjust table name

Measure = FORMAT(TIME(0,min('Table'[registered_time_mins]),0),"HH:MM:SS")

Re: Problems witha time format measure

Borjaveiga — Sat, 10 Feb 2024 11:19:17 GMT

Thanks for your quick answer, although that does not solve my problem, since that is a fact table and I need to sum all the minutes registred, its a fact table and I have several rows by Employee ID

Re: Problems witha time format measure

Dangar332 — Sat, 10 Feb 2024 13:12:59 GMT

hi, Borjaveiga

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Re: Problems witha time format measure

lbendlin — Sat, 10 Feb 2024 20:55:49 GMT

Not clear how you get from the sample data to the expected result . Can you please explain?

Re: Problems witha time format measure

Borjaveiga — Sun, 11 Feb 2024 12:49:58 GMT

Hi lbendlin Thanks for the interest, I have a column with time in minutes: registered_time_mins formated as wholenumber

I want to perform a sum of those min, since is the time logged by each worker.

After reading in different places in the community, I realized that Format does not work when the sum is over 24h

I am lookin for a solution that allows me to see per each worker what is the number of hours worked, even perform agregations by department, also be able to calculate averages, but also to format that minutes calculation in a way that is comprehensible by users with a display similar to:

90hour 30min or 90:30:00

Re: Problems witha time format measure

lbendlin — Sun, 11 Feb 2024 20:17:57 GMT

second request: Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523