Problems witha time format measure

[Borjaveiga]

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

[Borjaveiga]

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 

[lbendlin]

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-...

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/1447...

[Dangar332]

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")

[Borjaveiga]

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

[Dangar332]

hi, @Borjaveiga 

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

[lbendlin]

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-...

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/1447...

[Borjaveiga]

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

[lbendlin]

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