Calculate a HH:Min:Sec measure

[Babette]

I found the following HH:Min:Sec solution in this forum that helps me calculate exactly what i need but ...

Its a simple model that has LocationDIM, DateDIM and LoginsFact.

PBI Desktop report has a Table Visual with few slicers (Region, Country, Branch, Fiscal Year, Quarter) and columns like Region, Class, Branch, Login Name, Login_Duration_Sec (measure) etc.

Once I include Login_Duration_Formatted (measure) in the Table Visual, multiple Branches are displayed eventhough I select only ONE "Branch-A" in the slicer. All other branches Login_Duration_Formatted (measure) is 0. The totals at bottom shows the correct Login_Duration_Formatted (measure). But, it should not display all other branches in the Table Visual.

I modified my Login_Duration_Formatted (measure) calculation to troubleshoot and found that if I RETURN hours only, the Table Visual shows only "Branch-A' data. RETURN any > 1 items, multiple Branches appear again the Table Visual.

I added a "New Column Duration Formatted" = VAR allseconds ='Login_Duration'[Duration (sec)] ...followed by the rest of HH, Min and Sec calculations. When i added this to the Table Visual, it displays everything correctly ...but I am missing the total (at bottom) for the New Column Duration Formatted.

I am still new to Power Bi and don't know why Login_Duration_Formatted (measure) is not working for me.  Could you please help?

Login_Duration_Sec (measure) = sum('Login_Duration'[Duration (sec)])

Login_Duration_Formatted (measure) =
VAR allseconds = sum('Login_Duration'[Duration (sec)])

VAR hours = INT ( allseconds / 60 / 60 )
VAR minutes = MOD ( INT ( allseconds / 60 ), 60 )
VAR seconds = MOD ( allseconds, 60 )

RETURN hours & "h: " & minutes & "m: " & seconds & "s"

[ryan_mayu · Accepted solution]

@Babette 

pls try this

return TIME(hours,minutes,seconds)

[Babette]

@ryan_mayu  return TIME(hours,minutes,seconds) was working fine when loading 2 days of data. 

Once I increased to load 365 days data, the summarized TotalSec at month level for each staff's login ranged 55 to 291,411 seconds.

My original measure would need to change like this ...

Measure Shift Duration (formatted) =

VAR allseconds = SUM('LoginFact'[Login Duration (sec)])

VAR days = INT ( allseconds / 24 / 60 / 60 )
VAR hours = MOD ( INT ( allseconds / 60 / 60 ), 24 )
VAR minutes = MOD ( INT ( allseconds / 60 ), 60 )
VAR seconds = MOD ( allseconds, 60 )

RETURN time(days, hours, minutes, seconds)   --- this RETURN would not work as I have days in there now.

How do I RETURN days, hours, minutes, seconds??  Thank you.

[ryan_mayu]

@Babette 

i think you need to combine day and time, maybe you can try this

Measure Shift Duration (formatted) =

VAR allseconds = SUM('LoginFact'[Login Duration (sec)])

VAR days = INT ( allseconds / 24 / 60 / 60 )

VAR hours = MOD ( INT ( allseconds / 60 / 60 ), 24 )

VAR minutes = MOD ( INT ( allseconds / 60 ), 60 )

VAR seconds = MOD ( allseconds, 60 )

return days &"," & time(hours,minutes,seconds)

[Babette]

@ryan_mayu  The calculation validates and no error.  The measure became a string format instead of Date/Time format.  I noticed once it becomes a string format, the Table Visual displays (the behavior was fine when it was Return Time (...) 😞

1)  All the branches - it should not as I only selected Branch A in the Location slicer.

2)  Those other branches show no values in Shift Duration and formatted.

Please, any other suggestions?  Thank you.

[ryan_mayu]

@Babette 

Ｉ think we only modified the formatted column. what's the DAX of the shift duration sec?

The formatted column is based on the sec column. If your sec column is blank, the formatted column will show blank as well.

[Greg_Deckler]

@Babette After reading, I am not entirely sure what the issue is. Maybe some screen shots or the PBIX file in question would help? Perhaps the issue is you are returning text? Maybe use Chelsie Eiden's Duration? Chelsie Eiden's Duration - Microsoft Power BI Community

[Babette]

Thank you so much @Greg_Deckler and @ryan_mayu. 

Problem is resolved by " Return  TIME(hours,minutes,seconds) "

[ryan_mayu]

you are welcome