Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Babette
Helper III
Helper III

Calculate a HH:Min:Sec measure

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"

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@Babette 

pls try this

return TIME(hours,minutes,seconds)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

8 REPLIES 8
ryan_mayu
Super User
Super User

@Babette 

pls try this

return TIME(hours,minutes,seconds)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

 

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

 

Branch A results.png

@Babette 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Greg_Deckler
Super User
Super User

@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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thank you so much @Greg_Deckler and @ryan_mayu

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

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.