Forum Discussion

crispybc's avatar
crispybc
Frequent Visitor
3 years ago
Solved

Preview Feature Dynamic Formatting (time duration format)

I have seen the new preview feature for dynamic formatting and though I'd give it a go formating a duration measure (hours) into the format years,days,hours using the following formatting dax:

 

var col = [last status change duration]
var totaldays =  QUOTIENT(col,24)
var years = if (totaldays > 365, CONCATENATE(CONVERT(ROUNDDOWN(DIVIDE(totaldays,365),0),STRING),"y"))
var days = IF(MOD(totaldays, 365) >=1 , CONCATENATE(CONVERT(ROUNDDOWN(MOD(totaldays, 365),0),STRING),"d"))
var hours = if (MOD(col, 24) >=1, CONCATENATE(convert(TRUNC(MOD(col, 24)),STRING),"h"))
var string =  COMBINEVALUES(" ",years , days, hours)
return string

 

 

However, this is sometime returning incorrect values.

 

Not sure if i am doing something wrong, or it is just a bug with the new feature. Has anyone else managed to format a time duration measure into an easily readable format?

General Comment
Need Help
  • crispybc's avatar
    crispybc
    3 years ago

    I figured out the issue. If your output string contains a 0, then it is substituted out for the measure value. 

    You can work around this with the SUBSTITUTE function.

     

    So new solution:

    var col = [last status change duration]
    var totaldays =  ROUNDDOWN(DIVIDE(col,24),0)
    var years = if (totaldays > 365, CONCATENATE(CONVERT(ROUNDDOWN(DIVIDE(totaldays,365),0),STRING),"y"))
    var days = IF(MOD(totaldays, 365) >=1 , CONCATENATE(CONVERT(ROUNDDOWN(MOD(totaldays, 365),0),STRING),"d"))
    var hours = if (MOD(col, 24) >=1, CONCATENATE(convert(TRUNC(MOD(col, 24)),STRING),"h"))
    var string =  COMBINEVALUES(" ",years , days, hours)
    return SUBSTITUTE(string, "0", "\0")

     

1 Reply

  • crispybc's avatar
    crispybc
    Frequent Visitor
    3 years ago

    I figured out the issue. If your output string contains a 0, then it is substituted out for the measure value. 

    You can work around this with the SUBSTITUTE function.

     

    So new solution:

    var col = [last status change duration]
    var totaldays =  ROUNDDOWN(DIVIDE(col,24),0)
    var years = if (totaldays > 365, CONCATENATE(CONVERT(ROUNDDOWN(DIVIDE(totaldays,365),0),STRING),"y"))
    var days = IF(MOD(totaldays, 365) >=1 , CONCATENATE(CONVERT(ROUNDDOWN(MOD(totaldays, 365),0),STRING),"d"))
    var hours = if (MOD(col, 24) >=1, CONCATENATE(convert(TRUNC(MOD(col, 24)),STRING),"h"))
    var string =  COMBINEVALUES(" ",years , days, hours)
    return SUBSTITUTE(string, "0", "\0")