Increase efficiency of measure

[MadBern85]

Hi
I have direct query dataset of about 3000 rows. One of the rows contains an int-value of seconds.
In PBI I have made a measure which is just MAX(table[DurationInSecs]), but I've set this to be Dynamic format and in the format I have made this:

var _vSecs = SELECTEDMEASURE()

var _vMins = INT(DIVIDE(_vSecs, 60, 0))

var _vRemainingSecs = INT(MOD(_vSecs, 60))

var _vHours = INT(DIVIDE(_vMins, 60,0))

var _vRemainingMins = INT(MOD(_vMins, 60))

var _vDays = INT(DIVIDE(_vHours, 24, 0))

var _vRemainingHours = INT(MOD(_vHours, 24))

var _d = SUBSTITUTE(_vDays, "0", "\0")

var _h = SUBSTITUTE(_vRemainingHours, "0", "\0")

 var _m = SUBSTITUTE(_vRemainingMins, "0", "\0")

 var _s = SUBSTITUTE(_vRemainingSecs, "0", "\0")

 var _d_h_m_s = _d & "d " & _h & "h " & _m & "m, " & _s & "s"

 var _h_m_s = _h & "h " & _m & "m " & _s & "s"

 var _m_s = _m & "m " & _s & "s"

 var __s = _s & "s"

 RETURN

    SWITCH(TRUE(),

    _vSecs >= 86400, _d_h_m_s,

    _vSecs >= 3600, _h_m_s,

    _vSecs >= 60, _m_s,

    _s)

Strictly speaking, this works fine and produces the desired result, but it's quite slow.
So I'm looking for any tips to increase the efficiency.

Any input, anyone?

Regards,
Mads

[Anonymous]

HI @MadBern85 ,

You can try to use the following expressions if helps:

format = 
VAR _vTS = SELECTEDMEASURE()
VAR _vDays =
    INT ( DIVIDE ( _vTS, 86400, 0 ) )
VAR _vHours =
    INT ( DIVIDE ( _vTS - _vDays * 86400, 3600, 0 ) )
VAR _vMins =
    INT ( DIVIDE ( _vTS - _vDays * 86400 - _vHours * 3600, 60 ) )
VAR _vSecs = _vTS - _vDays * 86400 - _vHours * 3600 - _vMins * 60
RETURN
    IF ( _vTS > 86400, _vDays & "d " )
        & IF ( _vTS > 3600, _vHours & "h " )
        & IF ( _vTS > 60, _vMins & "m " ) & _vSecs & "s"

Regards,

Xiaoxin Sheng

[MadBern85]

Hi

Thanks for the suggestion, this is quite close to something I've already tried and the speed is much improved, but it produces some errors. Which I haven't been able to correct.

Regards,
Mads

[MadBern85 · Accepted solution]

However, you're suggestion gave me a better/more efficient code for the RETURN statement.
Changed my DAX to the following and the results good and the speed is much better.

Thanks a lot 🙂

var _vSecs = SELECTEDMEASURE()

var _vMins = INT(DIVIDE(_vSecs, 60, 0))

var _vRemainingSecs = INT(MOD(_vSecs, 60))

var _vHours = INT(DIVIDE(_vMins, 60,0))

var _vRemainingMins = INT(MOD(_vMins, 60))

var _vDays = INT(DIVIDE(_vHours, 24, 0))

var _vRemainingHours = INT(MOD(_vHours, 24))

var _d = SUBSTITUTE(_vDays, "0", "\0")

var _h = SUBSTITUTE(_vRemainingHours, "0", "\0")

 var _m = SUBSTITUTE(_vRemainingMins, "0", "\0")

 var _s = SUBSTITUTE(_vRemainingSecs, "0", "\0")

 RETURN

    IF ( _vSecs >= 86400, _d & "d " )

        & IF ( _vSecs >= 3600, _h & "h " )

        & IF ( _vSecs >= 60, _m & "m " ) & _s & "s"

Regards,
Mads