Forum Discussion

Prasannak75's avatar
Prasannak75
Frequent Visitor
2 years ago
Solved

MdxScript(Model) (5, 44) Calculation error in measure 'Queueltems'[TotaI Duration]:

I have the Seconds column name 'Queueltems'[TotaI Duration]' (Data type:- Whole number). I had created  a measure for summarizing the seconds in hh:mm:ss format 
Total Duration = FORMAT(TIME(0, 0,SUM(QueueItems[Queue Duration (Sec)])),"hh:mm:ss")

 

For 1 - 2 day it's was displaying the data when i select for more than 2 day's it's getting an visal error:- MdxScript(Model) (5, 44) Calculation error in measure 'Queueltems'[TotaI Duration]: An argument of function TIME' has the wrong data type or the result is too large or too small.

 

Can anyone help me with this issue? 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, Prasannak75 

    Of course, you can try follow measure:

     

    TotalDurationCustom = 
VAR TotalSeconds =
    SUM ( Queueltems[Queue Duration (Sec)] )
VAR TotalDays =
    INT ( TotalSeconds / 86400 )
VAR TotalHours =
    INT ( TotalSeconds / 3600 )
VAR RemainingSeconds =
    MOD ( TotalSeconds, 3600 )
VAR TotalMinutes =
    INT ( RemainingSeconds / 60 )
VAR FinalSeconds =
    MOD ( RemainingSeconds, 60 )
VAR _result1 =
    FORMAT ( TIME ( TotalHours, TotalMinutes, FinalSeconds ), "hh:mm:ss" )
VAR _result2 =
    FORMAT ( TIME ( TotalHours, TotalMinutes, FinalSeconds ), "hh:mm" )
RETURN
    SWITCH (
        TRUE (),
        TotalDays <= 0, _result1,
        TotalDays > 0
            && TotalDays < 10,
            "0" & TotalDays & ":" & _result2,
        TotalDays & ":" & _result2
    )

     


    No more than one day:

    More than one day:

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum



3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, Prasannak75 

    According to your description, you have a problem with MdxScript (Model) for visual objects, you can try the following to solve your problem.


    1. Make sure that your data are all integers and have no null values
    2. Each parameter of the Time function has a limit of 32767 for the maximum value, most likely this is the problem, you can customize the Measure to get rid of the limit.

    Measure:

    Total DurationCustom = 
VAR TotalSeconds = SUM(Queueltems[Queue Duration (Sec)])
VAR TotalHours = INT(TotalSeconds / 3600)
VAR RemainingSeconds = MOD(TotalSeconds, 3600)
VAR TotalMinutes = INT(RemainingSeconds / 60)
VAR FinalSeconds = MOD(RemainingSeconds, 60)
RETURN
    FORMAT(TIME(TotalHours, TotalMinutes, FinalSeconds), "hh:mm:ss")

    Before:

    After:

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

  • Prasannak75's avatar
    Prasannak75
    Frequent Visitor
    2 years ago

    It's not calculating greater than 24 hrs, Can you help me to create in the format of i.e ( 1 Day 2 Hrs 15mins) 

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi, Prasannak75 

      Of course, you can try follow measure:

       

      TotalDurationCustom = 
VAR TotalSeconds =
    SUM ( Queueltems[Queue Duration (Sec)] )
VAR TotalDays =
    INT ( TotalSeconds / 86400 )
VAR TotalHours =
    INT ( TotalSeconds / 3600 )
VAR RemainingSeconds =
    MOD ( TotalSeconds, 3600 )
VAR TotalMinutes =
    INT ( RemainingSeconds / 60 )
VAR FinalSeconds =
    MOD ( RemainingSeconds, 60 )
VAR _result1 =
    FORMAT ( TIME ( TotalHours, TotalMinutes, FinalSeconds ), "hh:mm:ss" )
VAR _result2 =
    FORMAT ( TIME ( TotalHours, TotalMinutes, FinalSeconds ), "hh:mm" )
RETURN
    SWITCH (
        TRUE (),
        TotalDays <= 0, _result1,
        TotalDays > 0
            && TotalDays < 10,
            "0" & TotalDays & ":" & _result2,
        TotalDays & ":" & _result2
    )

       


      No more than one day:

      More than one day:

       

      Best Regards,
      Yang
      Community Support Team

       

      If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
      If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

      How to get your questions answered quickly --  How to provide sample data in the Power BI Forum