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 - Calculation error cannot convert value of Type text to type number

 

MY measure which i am recieivng the error from is

Total activity new measure = CALCULATE('Calls SQL'[Total call time new mesaure] + 'Calls SQL'[Total wrap time new measure] + Breaks[Total make busy time new measure])
 
Measure 1
Total call time new mesaure = VAR Seconds1 = SUMX ( 'Calls SQL', SECOND ( 'Calls SQL'[Total Time on Calls] ) ) VAR Minutes1 = SUMX ( 'Calls SQL', MINUTE ( 'Calls SQL'[Total Time on Calls] ) ) VAR Hours1 = SUMX ( 'Calls SQL', HOUR ( 'Calls SQL'[Total Time on Calls] ) ) VAR Seconds2 = MOD ( Seconds1, 60 ) VAR Minutes2 = MOD ( TRUNC ( Seconds1 / 60 ) + Minutes1, 60 ) VAR Hours2 = Hours1 + TRUNC ( ( TRUNC ( Seconds1 / 60 ) + Minutes1 ) / 60 ) RETURN Hours2 & ":" & FORMAT ( Minutes2, "00" ) & ":" & FORMAT ( Seconds2, "00" )
 
Measure 2
Total wrap time new measure = VAR Seconds1 = SUMX ( 'Calls SQL', SECOND ( 'Calls SQL'[Wrap Time] ) ) VAR Minutes1 = SUMX ( 'Calls SQL', MINUTE ( 'Calls SQL'[Wrap Time] ) ) VAR Hours1 = SUMX ( 'Calls SQL', HOUR ( 'Calls SQL'[Wrap Time] ) ) VAR Seconds2 = MOD ( Seconds1, 60 ) VAR Minutes2 = MOD ( TRUNC ( Seconds1 / 60 ) + Minutes1, 60 ) VAR Hours2 = Hours1 + TRUNC ( ( TRUNC ( Seconds1 / 60 ) + Minutes1 ) / 60 ) RETURN Hours2 & ":" & FORMAT ( Minutes2, "00" ) & ":" & FORMAT ( Seconds2, "00" )
 
Measure 3
Total make busy time new measure = VAR Seconds1 = SUMX ( 'Breaks', SECOND ( 'Breaks'[Minutes] ) ) VAR Minutes1 = SUMX ( 'Breaks', MINUTE ( 'Breaks'[Minutes] ) ) VAR Hours1 = SUMX ( 'Breaks', HOUR ( 'Breaks'[Minutes] ) ) VAR Seconds2 = MOD ( Seconds1, 60 ) VAR Minutes2 = MOD ( TRUNC ( Seconds1 / 60 ) + Minutes1, 60 ) VAR Hours2 = Hours1 + TRUNC ( ( TRUNC ( Seconds1 / 60 ) + Minutes1 ) / 60 ) RETURN Hours2 & ":" & FORMAT ( Minutes2, "00" ) & ":" & FORMAT ( Seconds2, "00" )
 
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

6 Replies

  • 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