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

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.