Simple DAX formula to change decimal format in BI to hh:mm:ss format

[Anonymous]

Am in need of assistance of trying to simply convert decimal format in BI to hh:mm:ss... I cannot get this work. It does not validate the correct time... for me. Just a simple hour minute and second.

Avg Abandon Time
145.085
315.5393151
331.671875
334.1389583
467.5
306.2209615
373.5566667
324.2904082
379.1257576
251.2441935
280.9677778
179.4
243.9677778
270.2118182
344.6495833
320.6165
301.2883333
238.558
450.4476
258.1227273
346.2209091
235.5527586
315.8121212
284.5452
230.9918182
362.3925
397.0189474
206.63
293.7660377
261.0876087
326.3813158
353.8376786
318.3228571
199.4514286
469.85
319.5202667
313.9578333
288.6509091
342.1527273
260.8007143
291.600625
254.8984615
302.1284615
240.7615
268.6035714
398.4705882
372.5024
440.0081818
220.1325
304.4833333
335.8779412
378.3038889
284.57
247.9269697
185.1575
271.6466667
272.6404762
281.7368627
311.82
308.1389189
308.9951852
240.1296875
537.83
344.2498246
311.3548214
256.9675
338.3797436
243.0757143
161.5066667
149.206
357.2252941
284.9991667
327.0940741
265.7864
303.8321739
266.7733333
246.513
326.1015
339.4984
338.1
457.02
256.746875
199.4344444
308.2583333
284.69
330.4547619
306.5235714
356.5711111
385.5697101
315.1845
234.2223529
329.5866355
335.5938095
284.5131579
414.5257143
614.48
null
null
null
null
1.5
null
null
null
180.3233333
null
null
null
null
null
null
null
null
null
12.47
null
null
15687.68
null
null
null
189.2
null
3700.16
null
2873.0175
null
null
null
41.63
7.37
null
null
1015.25
null
15404.18333
null
null
null
null
4373.37
null
52
null
6004.77
null
2.47
null
null
null
null
74.80333333
207.73
1344.4
null
686.4275
null
null
null
290.27
null
491.19
null
300.615
null
null
616.63
null
null
null
null
83.635
null
106.67
394.13
null
null
null
null
56.16
726.1
null
14.54
null
null
78.87
null
10.97
1458.185
null
25.36
88.96
null
16900.99
null
69.63
null
null
451.7
81.9
null
null
null
null
null
null
null
null
null
null
7.66
null
4670.89
null
4354.764444
null
3657.550556
null
4675.556818
null
null
null
5347.426
null
4711.655
null
5285.53125
null
4757.521111
null
1689.932222
null
5630.665
null
null
4958.3225
null
4102.656
null
6189.64875
null
3684.557273
null
4542.937143
null
null
null
5365.4975
null
3061.305
null
5011.7
null
4864.716667
null
2738.711667
null
null
null
3759.4405
null
4319.580833
null
4896.75375
null
4963.168667
null
6351.48
null
null
null
4898.358788
null
4159.598462
null
10888.195
null
5563.728
null
7287.455
null
null
null
2061.455714
null
2116.890909
null
3614.649444
null
2315.389231
null
5206.22
null
null
null
2325.180909
null
10258.795
null
2251.332
null
3565.007273
null
5824.943333
null
null
null
null
null
3654.9125
null
4780.555
null
4260.904444
null
4252.906
null
null
null
5370.1525
null
4714.967857
null
4814.055
null
4930.819286
null
7062.605
null
null
null
3577.41625
null
3179.076
null
3481.93
null
6578.818333
null
14931.84
null
null
null
4532.313333
null
1969.061
null
7152.563
null
4424.503333
null
3398.23
null
null
null
4690.17
null
2148.724
null
5413.508
null
3564.186667
null
13999.32125
null
null
null
1770.08375
null
6444.399643
null
3422.81
null
3651.095
null
null
5406.578056
null
4005.334138
null
5374.276364
null
6992.574583
null
null
null
2798.428095
null
1651.486
null
2943.961
null
2613.025
null
7535.569333
null
null
null
747.7533333
null
2973.792857
3608.169167
null
3826.082308
null
2502.94
null
null
null
2401.602857
null
135.74
4369.464615
3351.727333
null
3291.421111
null
null
null
13474.39
null
4617.407
null
2486.83375
null
2143.836667
null
3100.112857
null
5872.1175
null
2603.56
null
3060.253333
null
3766.964444
2216.674
null
null
1217.866
202.05
null
213.6366667
3112.843333
null
1812.515
3.37
4961.832727
null
1921.34125
null
523.84
2508.334286
4154.837778
null
3270.74
null
1017.72
null
2742.748
221.3
null
null
240.935
null
32.1
null
null
null
null
896.27
null
null
null
null
null
null
698
918.1
null
null
null
507.41
null
620.97
null
586.02
null
null
null
null
null
null
630.8
null
null
null
null
792.77
null
null
null
null
null
185.6
null
134.2433333
null
50.67
849.5566667
null
1806.55
null
917.942
null
78.03333333
null
6508.565
null
null
null
null
1857.9
null
2310.7
null
null
null
47.6
null
null
null
7027.486
null
316.93
null
21228.815
null
178.6
null
120.48
null
null
null
305.58
null
38.5
null
null
null
40.05
null
null
null
null
null
31.2
null
null
null
548.23
null
null
4558.726667
null
null
null
131.22
null
6997.538333
null
null
154.0233333
null
3704.5
null
null
null
1559.4
null
null
null
3572.87
null
1.2
null
1470.656667
null
null
null
0.8
null
410.005
null
19529.5
null
null
null
12824.97
null
9.73
null
439.634
null
237.7766667
null
9.515
null
null
null
62.695
null
null
null
639.3633333
null
null
null
528.685
null
null
null
1100.92
null
null
null
5057.06
9084.053333
1391.2
null
null
null
302.135
null
4306.785
null
6042.332
null
96.97
null
222.15
null
null
null
null
18117.52
null
7543.55
null
545.66
null
14518.06417
null
null
null
null
1659.385
null
518.985
null
null
null
null
null
null
null
1190.1
null
null
null
null
null
null
null
977.51
9950.63
null
null
null
null
null
5066.173333
null
null
null
null
1006.2
null
null
null
null
null
null
6810.94
null
578.87
null
null
null
754.8
16973.54
null
null
null
166.6
null
null
51.07
null
4439.6
null
null
null
null
null
null
null
null
null
44.53
null
null
null
null
null
null
null
2236.9
null
null
null
null
205.17
null
4508.17
null
null
null
null
null
null
null
null
3729.735
null
null
null
null
null
1954.603333
null
null
null
null
null
367.4
159.215
null
null
null
null
null
null
null
null
null
null
null
null
4985
null
null
null
null
null
null
70.67
1376.93
3306.935
22.23
null
null
null
17471.96
null
42.2
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
null
37.26
null
null
null
null
null
null
null
1552.46
null
63.57
null
null
null
null
null
null
null
562.2566667
null
74.1
null
null
null
null
null
null
null
null
null
null
null
98.3
null
null
105.33
327.57
3.64
null
1214.93
null
null
null
38.43
80.235
41.2
null
1351.119286
1467.175
1490.065882
3531.303125
2949.205556
null
1663.336429
1968.056364
1043.910556
1278.896154
1937.764
null
null
524.1575
898.3991667
792.6585714
1392.04
2760.407857
null
1276
594.1
2702.998
1238.235882
null
1044.563636
1842.66
null
null
null
null
1000.618
null
1317.741176
1288.814231
1866.2536
2294.95619
null
null
1690.8345
1163.02875
null
1279.685455
773.77
1744.305
3559.6
null
null
null
1873.058636
null
2050.553158
2867.943684
2033.201622
null
2764.266667
null
null
null
1447.535
null
1622.079048
null
1513.493158
898.07
2377.972778
null
4921.908
null
null
3166.872
1093.656667
null
990.58
null
3833.127778
null
null
null
1930.391724
null
1890.765263
null
1920.432564
1537.582667
1845.966667
566.2
1622.46
null
2080.299286
null
1978.260833
1682.312
1758.3625
851.234
null
null
5129.93
null
1721.323
null
2856.046
577.654
null
1244.196667
2280.277857
null
null
null
1256.4095
1140.707692
4069.36
2020.195455
2005.217059
null
847.28
null
null
null
null
null
1833.81037
null
2222.653396
null
1863.018519
null

[watkinnc · Accepted solution]

Are we looking at decimal days, or are these hours, or minutes (or seconds)?

Since only seconds would make sense here, you would write:

Regular Time = FORMAT(DIVIDE(TableName[Abandon Time], 86400), "hh:mm:ss")

--Nate

[watkinnc]

Actually, my DAX does gives you the total duration of this column of seconds in hh:mm:ss, which is what the gentlemen requested. None of the values are going to be over a full day. Not sure why it's making a column instead of a measure.

In Power Query it's easy too, but it's then a highly unique and poorly compressed column. Nevertheless:

Table.TransformColumns(#"The Name Of Your Table", {{"Abandon Time", each if [Abandon Time] = null then null else  Time.From(Value.Divide([Abandon Time], 86400))}})

--Nate

[V-pazhen-msft · Accepted solution]

@Anonymous 
Yes found the error in my screenshort. Check the new pbix, you may open power query and double click steps to see details.

Here is what I have done.

1. Enter data as source in query editor.

2. Power query changes the type automatically to decimal numbers, if not working try change "." to "," as decimal separator.

3. And the custom column.

=#duration(0,0,0,[Seconds])

4. Replace error with null

5. Split the column with right most ".", so you have HHMMSS and decimal values (microseconds?). Then close and apply to get the following.

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[V-pazhen-msft]

@Anonymous 

You are not getting the full time with the DAX. Power Query method is straight forward.

Add a custom column, then split the days with HHMMSS.

Close & Apply.

PaulZheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@V-pazhen-msft and  First of all thanks for responding to my post.
I am so new at this and I am not quite getting it.  It shows in your screenshot a column that looks like seconds, but my column populates as decimal so when I try to add the custom column, it returns an error.
Your pbix shows the data populated but I can't seem to replicate. Can you maybe type out a step by step. Sorry to be so new at this.  Thanks

@V-pazhen-msft In your screenshot

[watkinnc]

This is a measure. 

[Anonymous]

@watkinnc When I try to use the data and select measure it gives me an error. However if I create a column, the formula works but I am not sure if that is correct or not. Can you walk me through what you mean when you use a measure?

[watkinnc · Accepted solution]

Are we looking at decimal days, or are these hours, or minutes (or seconds)?

Since only seconds would make sense here, you would write:

Regular Time = FORMAT(DIVIDE(TableName[Abandon Time], 86400), "hh:mm:ss")

--Nate

[Anonymous]

This decimal data comes Ring Central data. I am pretty sure it is not days, it is per call so I am assuming seconds.

[Anonymous]

Would this be a measure or a new column?