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

[Prasannak75]

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? 

[Prasannak75]

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 · Accepted solution]

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

[Anonymous]

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