Forum Discussion
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.
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
BICrazy ok no worries, however the second script i posted does work.
9 Replies
- vanessafvgCommunity Champion
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 )))- BICrazyHelper II
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.
- vanessafvgCommunity Champion
BICrazy what error are you getting? you can return what you want, setting the variable doesn't mean you have to return it