cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BICrazy Frequent Visitor
Frequent Visitor

Drop Seconds from Duration Measure

Hi,

 

I'm very new to this forum and still a basic learner of Power BI.  I'm using the following 2 measures to display Duration as Hours minutes and seconds.

 

1st Measure:

Duration2Seconds =

VAR sub = SUBSTITUTE(MAX('TrackerDailySummary'[Duration]),":","|")

VAR hours = VALUE(PATHITEM(sub,1))*3600
VAR minutes = VALUE(PATHITEM(sub,2))*60
VAR seconds = VALUE(PATHITEM(sub,3))*1

RETURN hours+minutes+seconds

 

2nd Measure:

Convert to Seconds =
VAR Duration = SUMX(TrackerDailySummary,[Duration2Seconds])
VAR Hours = INT(Duration/3600)
VAR Minutes = INT(MOD(Duration,3600)/60)
VAR Seconds = MOD(Duration,60)

VAR H =
IF ( LEN ( Hours ) = 1,
CONCATENATE ( "0", Hours ),
CONCATENATE ( "", Hours )
)
// Minutes with leading zeros
VAR M =
IF (
LEN ( Minutes ) = 1,
CONCATENATE ( "0", Minutes ),
CONCATENATE ( "", Minutes )
)
// Seconds with leading zeros
VAR S =
IF (
LEN ( Seconds ) = 1,
CONCATENATE ( "0", Seconds ),
CONCATENATE ( "", Seconds )
)


VAR tH = IF(Hours=0,"",IF(Hours>1,Hours&" Hours, ",Hours&" Hour, "))
VAR tM = IF(Minutes=0,"",IF(Minutes>1,Minutes&" Minutes, ",Minutes&" Minute, "))
VAR tS = IF(Seconds=0,"",IF(Seconds>1,Seconds&" Seconds",Seconds&" Second"))

RETURN
CONCATENATE (th, CONCATENATE ("",CONCATENATE (tm,CONCATENATE ("", CONCATENATE (ts,"" )) )))

 

These are the results I'm returning, which is accurate.  

pic.JPG

 

I'd like to drop off the seconds and just display the hours and minutes only.  Any suggestions on how I can adjust this code?

 

Regards

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I
Super User I

Re: Drop Seconds from Duration Measure

@BICrazy ok no worries, however the second script i posted does work.


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!

View solution in original post

9 REPLIES 9
Super User I
Super User I

Re: Drop Seconds from Duration Measure

@BICrazy

 

you should just return your variables without the ts

 

VAR tH = IF(Hours=0,"",IF(Hours>1,Hours&" Hours, ",Hours&" Hour, "))
VAR tM = IF(Minutes=0,"",IF(Minutes>1,Minutes&" Minutes, ",Minutes&" Minute, "))
VAR tS = IF(Seconds=0,"",IF(Seconds>1,Seconds&" Seconds",Seconds&" Second"))

RETURN
CONCATENATE (th, CONCATENATE ("",CONCATENATE (tm,CONCATENATE )))


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
BICrazy Frequent Visitor
Frequent Visitor

Re: Drop Seconds from Duration Measure

Hi Vanessafvg,

 

I tried not returning the seconds and the code doesn't work after that.  I think the entire needs to accommodate dropping the seconds.

Super User I
Super User I

Re: Drop Seconds from Duration Measure

@BICrazy what error are you getting?  you can return what you want, setting the variable doesn't mean you have to return it


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Super User I
Super User I

Re: Drop Seconds from Duration Measure

@BICrazy does your code run with you original script?  


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
BICrazy Frequent Visitor
Frequent Visitor

Re: Drop Seconds from Duration Measure

@vanessafvg 

If I don't return the seconds, I receive the following error: Failed to resolve name 'CONCATENATE'. It is not a valid table, variable, or function name

The 2 codes that I posted are created as 2 separate measures and the latter is dependent in the 1st measure.

Super User I
Super User I

Re: Drop Seconds from Duration Measure

@BICrazy are you able to share your file with me?


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Super User I
Super User I

Re: Drop Seconds from Duration Measure

@BICrazy

 

Convert to Seconds =
VAR Duration =
    SUMX ( TrackerDailySummary, [Duration2Seconds] )
VAR Hours =
    INT ( Duration / 3600 )
VAR Minutes =
    INT ( MOD ( Duration3600 ) / 60 )
VAR Seconds =
    MOD ( Duration60 )
VAR H =
    IF ( LEN ( Hours ) = 1CONCATENATE ( "0"Hours )CONCATENATE ( ""Hours ) ) // Minutes with leading zeros
VAR M =
    IF (
        LEN ( Minutes ) = 1,
        CONCATENATE ( "0"Minutes ),
        CONCATENATE ( ""Minutes )
    ) // Seconds with leading zeros
VAR S =
    IF (
        LEN ( Seconds ) = 1,
        CONCATENATE ( "0"Seconds ),
        CONCATENATE ( ""Seconds )
    )
VAR tH =
    IF ( Hours = 0""IF ( Hours > 1Hours & " Hours, "Hours & " Hour, " ) )
VAR tM =
    IF (
        Minutes = 0,
        "",
        IF ( Minutes > 1Minutes & " Minutes, "Minutes & " Minute, " )
    )
VAR tS =
    IF (
        Seconds = 0,
        "",
        IF ( Seconds > 1Seconds & " Seconds"Seconds & " Second" )
    )
RETURN
    CONCATENATE ( thCONCATENATE ( ""CONCATENATE ( tm, CONCATENATE ) ) )


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
BICrazy Frequent Visitor
Frequent Visitor

Re: Drop Seconds from Duration Measure

@vanessafvg

 

Thanks for all your assistance.  I initially thought that I was doing something wrong.

I've managed to convert the duration to hours and minutes by removing seconds from the return on the 1st Measure.  This drops the overall time but I figured that I'm dropping all the seconds from each individual record in my data which was adding up to minutes. 


Super User I
Super User I

Re: Drop Seconds from Duration Measure

@BICrazy ok no worries, however the second script i posted does work.


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors