cancel
Showing results for
Did you mean:
Highlighted
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 )
)
VAR M =
IF (
LEN ( Minutes ) = 1,
CONCATENATE ( "0", Minutes ),
CONCATENATE ( "", Minutes )
)
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.

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

## Re: Drop Seconds from Duration Measure

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

Proud a to be a Datanaut!
9 REPLIES 9
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 )))

Proud a to be a Datanaut!
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

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

Proud a to be a Datanaut!
Super User I

## Re: Drop Seconds from Duration Measure

@BICrazy does your code run with you original script?

Proud a to be a Datanaut!
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

## Re: Drop Seconds from Duration Measure

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

Proud a to be a Datanaut!
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 ) ) )

Proud a to be a Datanaut!
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

## Re: Drop Seconds from Duration Measure

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

Proud a to be a Datanaut!

Announcements

#### 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?

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

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!