Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@BICrazy ok no worries, however the second script i posted does work.
Proud to be a Super User!
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 to be a Super User!
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.
@BICrazy what error are you getting? you can return what you want, setting the variable doesn't mean you have to return it
Proud to be a Super User!
@BICrazy does your code run with you original script?
Proud to be a Super User!
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 ) ) )
Proud to be a Super User!
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.
@BICrazy ok no worries, however the second script i posted does work.
Proud to be a Super User!
@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.
@BICrazy are you able to share your file with me?
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
148 | |
105 | |
104 | |
89 | |
65 |