Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Time Measure sum Time Measure error

Hi Guys,   I'm trying to sum the three below measures together, which forum member Icey kindly help with,  but an recieving the following error.   Couldnt load the data for the visual - Calculati...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous,

    I found a typo on my formula in 'tsBK' variable('Calls SQL' should be 'Breaks' and I already modify on above post), you can modify measure formula to confirm if the wrong calculation is caused with this part.

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous,

Current DAX formulas do not support to use math operator on text values.
According to your DAX formulas, they will return merges text values that not able to do math calculation, you can only use text operation functions and logic operators to compare with different text values.

DAX operators 

Please keep your duration as a numeric value(total second) and convert them after calculations instead convert before calculations.

Aggregating Duration Time 

Regards,

Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Ok that makes sense thanks for that, could you help with the formula??

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      HI Anonymous ,

      You can try to use the following measure formula:

       

      Total time =
VAR tsCS =
    SUMX (
        'Calls SQL',
        VAR _t1 =
            TIMEVALUE ( 'Calls SQL'[Total Time on Calls] )
        VAR _t2 =
            TIMEVALUE ( 'Calls SQL'[Wrap Time] )
        RETURN
            ( HOUR ( _t1 ) + HOUR ( _t2 ) ) * 3600
                + ( MINUTE ( _t1 ) + MINUTE ( _t2 ) ) * 60
                + SECOND ( _t1 )
                + SECOND ( _t2 )
    )
VAR tsBK =
    SUMX (
        'Breaks',
        VAR _t1 =
            TIMEVALUE ( 'Breaks'[Minutes] )
        RETURN
            HOUR ( _t1 ) * 3600
                + MINUTE ( _t1 ) * 60
                + SECOND ( _t1 )
    )
VAR ts = tsCS + tsBK
VAR _hour =
    INT ( ts / 3600 )
VAR _minute =
    INT ( MOD ( ts, 3600 ) / 60 )
VAR _second = ts - _hour * 3600 - _minute * 60
RETURN
    _hour & ":"
        & FORMAT ( _minute, "00" ) & ":"
        & FORMAT ( _second, "00" )

       

      Regards,

      Xiaoxin Sheng

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Thanks for this Anonymous .  I seem to be getting different results to what excel would show as a total. 

         

        Excel = 48:47:31

        Power BI Measure = 499:47:08