SLA Help : How to convert minutes to DDHHMMSS in Direct Query

[Shree_185]

Hello everyone - I need your help to convert Minutes to DDHHMMSS format. As I am using an SQL server direct query Format function doesn't support it. Can anyone help me here?

[Anonymous · Accepted solution]

Hi @Shree_185 ,

Format function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

You can create a measure using format function.

Format Measure = 
VAR _day =
    QUOTIENT ( MAX ( 'minutes'[Minutes] ), 60 * 24 )
VAR _hour =
    FORMAT ( QUOTIENT ( MOD ( MAX ( 'minutes'[Minutes] ), 60 * 24 ), 60 ), "00" )
VAR _minute =
    FORMAT ( MOD ( MAX ( 'minutes'[Minutes] ), 60 ), "00" )
RETURN
    _day & "day " & _hour & ":" & _minute & ":" & "00"

If you want a calculated column, you can create like

Format Column = 
VAR _day =
    QUOTIENT ( [Minutes] , 60 * 24 )
VAR _hour =
     QUOTIENT ( MOD ( 'minutes'[Minutes] , 60 * 24 ), 60 )
VAR _minute =
  MOD ( [Minutes] , 60 )
RETURN
    _day & "day " & _hour & ":" & _minute & ":" & "00"

But it does not look as beautiful as the metric, because the FORMAT function cannot be used.

You can add IF function to judge.

Format Column =
VAR _day =
    QUOTIENT ( [Minutes], 60 * 24 )
VAR _hour =
    QUOTIENT ( MOD ( 'minutes'[Minutes], 60 * 24 ), 60 )
VAR _minute =
    MOD ( [Minutes], 60 )
RETURN
    IF (
        _hour >= 10
            && _minute >= 10,
        _day & "day " & _hour & ":" & _minute & ":" & "00",
        IF (
            _hour >= 10
                && _minute < 10,
            _day & "day " & _hour & ":0" & _minute & ":" & "00",
            IF (
                _hour < 10
                    && _minute >= 10,
                _day & "day 0" & _hour & ":" & _minute & ":" & "00"
            )
        )
    )

You can check more details from the attachment.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Shree_185 , Try in power query

#duartion(0,0,[Minutes],0)

or a text column using DAX

Quotient([Minutes],60*24) & "days " Quotient(mod([Minutes],60*24),60) &":" & Quotient(mod(mod([Minutes],60*24),60),60) &":0"