Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have six columns to convert from decimal value of seconds to dd:hh:mm:ss.
I used the following code below to convert four of my columns.
Logged-In Time =
var Duration = [Avg Logged-In Time In Seconds]
VAR Days =
INT ( Duration / 86400)
VAR Hours =
INT ( MOD( Duration - ( Days * 86400),86400) / 3600)
VAR Minutes =
INT (MOD (MOD( Duration - (Days * 86400 ),86400 ), 3600 ) / 60)
VAR Seconds =
ROUNDUP(MOD ( MOD( MOD ( Duration - (Days * 86400 ),86400 ), 3600 ), 60), 0)
VAR D =
IF ( LEN ( Days ) = 1,
CONCATENATE ( "0", Days ),
CONCATENATE ( "", Days )
)
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 )
)
RETURN
CONCATENATE (
D,
CONCATENATE ( ":", CONCATENATE ( H, CONCATENATE( ":", CONCATENATE (M, CONCATENATE ( ":", S ) ) ) )
))
The code worked, however I could not use the data as additives. I need the totals, the sums, and averages.
Furthermore,
Columns 5 and 6 however did not convert from Text to Time and I do not know why.
All six columns I converted are using a decimal format.
The only difference that I can see is that the numbers in the columns 5(Not Ready Time) &6(Logged-In Time) are much bigger.
Any advice would be greatly appreciated.
Goals: I need columns 5&6 converted to Time
I need to be able to use the six columns for their totals, averages and sums.
Solved! Go to Solution.
OK, so no need to create the calculated columns using the formatting code. Instead, apply your code to a SUM measure (Or any other aggregation measure) for example:
Then create the measures
Sum Duration measure = SUM('Table'[Duration in seconds])
and using your code...
Formatted measure =
var Duration = [Sum Duration measure]
VAR Days =
INT ( Duration / 86400)
VAR Hours =
INT ( MOD( Duration - ( Days * 86400),86400) / 3600)
VAR Minutes =
INT (MOD (MOD( Duration - (Days * 86400 ),86400 ), 3600 ) / 60)
VAR Seconds =
ROUNDUP(MOD ( MOD( MOD ( Duration - (Days * 86400 ),86400 ), 3600 ), 60), 0)
VAR D =
IF ( LEN ( Days ) = 1,
CONCATENATE ( "0", Days ),
CONCATENATE ( "", Days )
)
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 )
)
RETURN
CONCATENATE (
D,
CONCATENATE ( ":", CONCATENATE ( H, CONCATENATE( ":", CONCATENATE (M, CONCATENATE ( ":", S ) ) ) )
))
you can then...
Sample PBIX attached
Proud to be a Super User!
Paul on Linkedin.
"I need to be able to use the six columns for their totals, averages and sums".
You are going to need to apply the formatting you need to the actual measures used in the visuals (the measures are calculated on the numerical fields), and visuals which display values (Clustered column charts, bar charts...) will not work with text fields as values.
Proud to be a Super User!
Paul on Linkedin.
You're right Paul, that is precisely what I need help doing.
I don't have visuals yet because I need time-formatted data.
Is there a way to format the text values into duration format? Did you see my code?
OK, so no need to create the calculated columns using the formatting code. Instead, apply your code to a SUM measure (Or any other aggregation measure) for example:
Then create the measures
Sum Duration measure = SUM('Table'[Duration in seconds])
and using your code...
Formatted measure =
var Duration = [Sum Duration measure]
VAR Days =
INT ( Duration / 86400)
VAR Hours =
INT ( MOD( Duration - ( Days * 86400),86400) / 3600)
VAR Minutes =
INT (MOD (MOD( Duration - (Days * 86400 ),86400 ), 3600 ) / 60)
VAR Seconds =
ROUNDUP(MOD ( MOD( MOD ( Duration - (Days * 86400 ),86400 ), 3600 ), 60), 0)
VAR D =
IF ( LEN ( Days ) = 1,
CONCATENATE ( "0", Days ),
CONCATENATE ( "", Days )
)
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 )
)
RETURN
CONCATENATE (
D,
CONCATENATE ( ":", CONCATENATE ( H, CONCATENATE( ":", CONCATENATE (M, CONCATENATE ( ":", S ) ) ) )
))
you can then...
Sample PBIX attached
Proud to be a Super User!
Paul on Linkedin.
The bit of quick testing I did seems to indicate the issue is BI does not know how to convert the day portion of your text string (DD:hh:nn:ss) to a datetime format.
For a work around can you use measures as opposed to calculated columns and then use the text formatted measures in your visuals?
An example based on your data
Proud to be a Super User! | |
So I put in the code you provided above as a measure, however it is giving me the message
"MdxScript(Model)(46, 23) Calculation error in measure 'FACT AGENT'[Average Logged In Time]: The Function AVERAGEX cannot work with values of type String."
Yep.
I assumed that the average logged time in seconds was (or could be) formatted as a number. At that point all the math could be done on the seconds (formatted as numbers) then converted to the text format that was desired.
Proud to be a Super User! | |
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |