Drop Seconds from Duration Measure

[BICrazy]

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

[vanessafvg]

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

[BICrazy]

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.

[vanessafvg]

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

[vanessafvg]

@BICrazy does your code run with you original script?  

[vanessafvg]

@BICrazy

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

[BICrazy]

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

[vanessafvg · Accepted solution]

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

[BICrazy]

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

[vanessafvg]

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