TIME() function argument problem

[-Ozym4nd1az-]

Hi,

The following DAX has a problem in the function 'An argument of function 'TIME' has the wrong data type or the result is too large or too small'.

This error appears only when I introduce (34200-(66600-(HOUR('Incident Data'[Open time])*3600+MINUTE('Incident Data'[Open time])*60+SECOND('Incident Data'[Open time]))))) in the argument of TIME function.

I really need this calculation in the seconds argument... I tried a lot of workarounds, but nothing works (tried to create a function with variables and them reference to it, no success).

Max. response time column =

if(CALCULATE(COUNT('Incident Data'[Priority]), 'Incident Data'[Priority] == "4 - Low") && (HOUR('Incident Data'[Open time])*3600+MINUTE('Incident Data'[Open time])*60+SECOND('Incident Data'[Open time]))>=59400 && ('Incident Data'[Reaction time]+(HOUR('Incident Data'[Open time])*3600+MINUTE('Incident Data'[Open time])*60+SECOND('Incident Data'[Open time])))>66600, if(((34200-(66600-(HOUR('Incident Data'[Open time])*3600+MINUTE('Incident Data'[Open time])*60+SECOND('Incident Data'[Open time]))))>0), TIME(0,0,(34200-(66600-(HOUR('Incident Data'[Open time])*3600+MINUTE('Incident Data'[Open time])*60+SECOND('Incident Data'[Open time]))))), TIME(0,0,0)),

if(CALCULATE(COUNT('Incident Data'[Priority]), 'Incident Data'[Priority] == "3 - Medium") && (HOUR('Incident Data'[Open time])*3600+MINUTE('Incident Data'[Open time])*60+SECOND('Incident Data'[Open time]))>=59400 && ('Incident Data'[Reaction time]+(HOUR('Incident Data'[Open time])*3600+MINUTE('Incident Data'[Open time])*60+SECOND('Incident Data'[Open time])))>63000, if(((30600-(66600-(HOUR('Incident Data'[Open time])*3600+MINUTE('Incident Data'[Open time])*60+SECOND('Incident Data'[Open time]))))>0), TIME(3,3,3), TIME(0,0,0)),

if(CALCULATE(COUNT('Incident Data'[Priority]), 'Incident Data'[Priority] == "2 - High") && (HOUR('Incident Data'[Open time])*3600+MINUTE('Incident Data'[Open time])*60+SECOND('Incident Data'[Open time]))>=59400 && ('Incident Data'[Reaction time]+(HOUR('Incident Data'[Open time])*3600+MINUTE('Incident Data'[Open time])*60+SECOND('Incident Data'[Open time])))>61200, if(((28800-(66600-(HOUR('Incident Data'[Open time])*3600+MINUTE('Incident Data'[Open time])*60+SECOND('Incident Data'[Open time]))))>0), TIME(2,2,2), TIME(0,0,0)),

if(CALCULATE(COUNT('Incident Data'[Priority]), 'Incident Data'[Priority] == "1 - Very High") && (HOUR('Incident Data'[Open time])*3600+MINUTE('Incident Data'[Open time])*60+SECOND('Incident Data'[Open time]))>=59400 && ('Incident Data'[Reaction time]+(HOUR('Incident Data'[Open time])*3600+MINUTE('Incident Data'[Open time])*60+SECOND('Incident Data'[Open time])))>60300, if(((27900-(66600-(HOUR('Incident Data'[Open time])*3600+MINUTE('Incident Data'[Open time])*60+SECOND('Incident Data'[Open time]))))>0),TIME(1,1,1),TIME(0,0,0)),

if(CALCULATE(COUNT('Incident Data'[Priority]), 'Incident Data'[Priority] == "4 - Low"), [Next business time column]+ TIME(0,0,7200), if(CALCULATE(COUNT('Incident Data'[Priority]), 'Incident Data'[Priority] == "3 - Medium"), [Next business time column]+ TIME(0,0,3600), if(CALCULATE(COUNT('Incident Data'[Priority]), 'Incident Data'[Priority] == "2 - High"), [Next business time column]+ TIME(0,0,1800), if(CALCULATE(COUNT('Incident Data'[Priority]), 'Incident Data'[Priority] == "1 - Very High"), [Next business time column]+ TIME(0,0,900), 0

))))))))

That's my dataset, it returns 04:04:04, 03:03:03, 02:02:02 and 01:01:01 in 'Max. response time column'.

Could you help me?

[lbendlin]

Tried to put this in DAXFormatter.com but it didn't help much.

Max. response time column =
IF (
    CALCULATE (
        COUNT ( 'Incident Data'[Priority] ),
        'Incident Data'[Priority] == "4 - Low"
    )
        && (
            HOUR ( 'Incident Data'[Open time] ) * 3600
                + MINUTE ( 'Incident Data'[Open time] ) * 60
                + SECOND ( 'Incident Data'[Open time] )
        ) >= 59400
        && (
            'Incident Data'[Reaction time]
                + (
                    HOUR ( 'Incident Data'[Open time] ) * 3600
                        + MINUTE ( 'Incident Data'[Open time] ) * 60
                        + SECOND ( 'Incident Data'[Open time] )
                )
        ) > 66600,
    IF (
         (
             (
                34200
                    - (
                        66600
                            - (
                                HOUR ( 'Incident Data'[Open time] ) * 3600
                                    + MINUTE ( 'Incident Data'[Open time] ) * 60
                                    + SECOND ( 'Incident Data'[Open time] )
                            )
                    )
            ) > 0
        ),
        TIME ( 0, 0, (
            34200
                - (
                    66600
                        - (
                            HOUR ( 'Incident Data'[Open time] ) * 3600
                                + MINUTE ( 'Incident Data'[Open time] ) * 60
                                + SECOND ( 'Incident Data'[Open time] )
                        )
                )
        ) ),
        TIME ( 0, 0, 0 )
    ),
    IF (
        CALCULATE (
            COUNT ( 'Incident Data'[Priority] ),
            'Incident Data'[Priority] == "3 - Medium"
        )
            && (
                HOUR ( 'Incident Data'[Open time] ) * 3600
                    + MINUTE ( 'Incident Data'[Open time] ) * 60
                    + SECOND ( 'Incident Data'[Open time] )
            ) >= 59400
            && (
                'Incident Data'[Reaction time]
                    + (
                        HOUR ( 'Incident Data'[Open time] ) * 3600
                            + MINUTE ( 'Incident Data'[Open time] ) * 60
                            + SECOND ( 'Incident Data'[Open time] )
                    )
            ) > 63000,
        IF (
             (
                 (
                    30600
                        - (
                            66600
                                - (
                                    HOUR ( 'Incident Data'[Open time] ) * 3600
                                        + MINUTE ( 'Incident Data'[Open time] ) * 60
                                        + SECOND ( 'Incident Data'[Open time] )
                                )
                        )
                ) > 0
            ),
            TIME ( 3, 3, 3 ),
            TIME ( 0, 0, 0 )
        ),
        IF (
            CALCULATE (
                COUNT ( 'Incident Data'[Priority] ),
                'Incident Data'[Priority] == "2 - High"
            )
                && (
                    HOUR ( 'Incident Data'[Open time] ) * 3600
                        + MINUTE ( 'Incident Data'[Open time] ) * 60
                        + SECOND ( 'Incident Data'[Open time] )
                ) >= 59400
                && (
                    'Incident Data'[Reaction time]
                        + (
                            HOUR ( 'Incident Data'[Open time] ) * 3600
                                + MINUTE ( 'Incident Data'[Open time] ) * 60
                                + SECOND ( 'Incident Data'[Open time] )
                        )
                ) > 61200,
            IF (
                 (
                     (
                        28800
                            - (
                                66600
                                    - (
                                        HOUR ( 'Incident Data'[Open time] ) * 3600
                                            + MINUTE ( 'Incident Data'[Open time] ) * 60
                                            + SECOND ( 'Incident Data'[Open time] )
                                    )
                            )
                    ) > 0
                ),
                TIME ( 2, 2, 2 ),
                TIME ( 0, 0, 0 )
            ),
            IF (
                CALCULATE (
                    COUNT ( 'Incident Data'[Priority] ),
                    'Incident Data'[Priority] == "1 - Very High"
                )
                    && (
                        HOUR ( 'Incident Data'[Open time] ) * 3600
                            + MINUTE ( 'Incident Data'[Open time] ) * 60
                            + SECOND ( 'Incident Data'[Open time] )
                    ) >= 59400
                    && (
                        'Incident Data'[Reaction time]
                            + (
                                HOUR ( 'Incident Data'[Open time] ) * 3600
                                    + MINUTE ( 'Incident Data'[Open time] ) * 60
                                    + SECOND ( 'Incident Data'[Open time] )
                            )
                    ) > 60300,
                IF (
                     (
                         (
                            27900
                                - (
                                    66600
                                        - (
                                            HOUR ( 'Incident Data'[Open time] ) * 3600
                                                + MINUTE ( 'Incident Data'[Open time] ) * 60
                                                + SECOND ( 'Incident Data'[Open time] )
                                        )
                                )
                        ) > 0
                    ),
                    TIME ( 1, 1, 1 ),
                    TIME ( 0, 0, 0 )
                ),
                IF (
                    CALCULATE (
                        COUNT ( 'Incident Data'[Priority] ),
                        'Incident Data'[Priority] == "4 - Low"
                    ),
                    [Next business time column] + TIME ( 0, 0, 7200 ),
                    IF (
                        CALCULATE (
                            COUNT ( 'Incident Data'[Priority] ),
                            'Incident Data'[Priority] == "3 - Medium"
                        ),
                        [Next business time column] + TIME ( 0, 0, 3600 ),
                        IF (
                            CALCULATE (
                                COUNT ( 'Incident Data'[Priority] ),
                                'Incident Data'[Priority] == "2 - High"
                            ),
                            [Next business time column] + TIME ( 0, 0, 1800 ),
                            IF (
                                CALCULATE (
                                    COUNT ( 'Incident Data'[Priority] ),
                                    'Incident Data'[Priority] == "1 - Very High"
                                ),
                                [Next business time column] + TIME ( 0, 0, 900 ),
                                0
                            )
                        )
                    )
                )
            )
        )
    )
)

Can you maybe explain what you are trying to measure?

[-Ozym4nd1az-]

Hi Ibendlin,

I have calculation that is counting how many seconds of the past biz day is remaining for the morning in the next day, considering the severity of incidents and if they were opened before the due time to the end of the day. E.g. if a incident is sev. 2 (2 hours to for reaction time), and it is opened after 16h30 (16h44 for instance), I should add 14 minutes to the max. time response so it should be 7h44.

I debugged the code and all works. Except for the TIME function with the argument for this calculation. This calculation below returns me the remaining time. A condition before ensures that if the time is negative it registers 00:00:00 and them I can could it as 'breached'.

Normally TIME() is accepting any number as argument in seconds (e.g. 28959) and converting it to data time. However, when I insert the calculation below for it, it just doesn't accept as argument. and the tile breaks. So, that's the reason I am using TIME(2,2,2) in some parte of code, just to debug. I need to make possible that TIME(), accepts somehow this function above (which returns a number in seconds too!).

34200-(66600-(HOUR('Incident Data'[Open time])*3600+MINUTE('Incident Data'[Open time])*60+SECOND('Incident Data'[Open time])))

BR,

Pedro