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,SU...
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



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