Expression that yeild variant data-type error

[surajde]

Hi All,

Need your help with below data type where I'm getting error when trying to apply a dax on Met as a conditional column. 

Sample Data: 

OPENED_DATE_CST	RESTORED_DATE_CST	CLOSED_DATE_CST	TICKET_STATE	TTR_Hours	Target (Hours)	Days to Due Date	Met
12/1/21 12:02 AM	12/2/21 12:39 PM	12/7/21 12:41 PM	Closed	12.83	168.00	7.00
12/1/21 12:31 AM	12/14/21 2:38 PM	12/19/21 5:12 PM	Closed	62.54	168.00	7.00
12/1/21 12:59 AM	12/3/21 12:36 PM	12/8/21 12:40 PM	Closed	35.46	168.00	7.00

When I created a calculated column for last condition to avoid addition of dates then received another error: 

[surajde]

Hi @johnt75 , 

Please see below formula for GreaterThanOpenDue column. basically I then tried creating to avoid last calculation of dates 

Met =

    IF('AOTS Daily CSL 54 1'[TICKET_STATE]="Cancel","NA",

    IF(OR(

        'AOTS Daily CSL 54 1'[TICKET_STATE]="Closed",

        'AOTS Daily CSL 54 1'[TICKET_STATE]="Ready to Close"),

        IF('AOTS Daily CSL 54 1'[TTR_Hours]<='AOTS Daily CSL 54 1'[Target (Hours)],1),

        IF('AOTS Daily CSL 54 1'[OPENED_DATE_CST]+'AOTS Daily CSL 54 1'[Days to Due Date]>TODAY(),"TBD",0))

[johnt75 · Accepted solution]

Try

Met =
IF (
    'AOTS Daily CSL 54 1'[TICKET_STATE] = "Cancel",
    "NA",
    IF (
        OR (
            'AOTS Daily CSL 54 1'[TICKET_STATE] = "Closed",
            'AOTS Daily CSL 54 1'[TICKET_STATE] = "Ready to Close"
        ),
        IF (
            'AOTS Daily CSL 54 1'[TTR_Hours] <= 'AOTS Daily CSL 54 1'[Target (Hours)],
            "1"
        ),
        IF (
            'AOTS Daily CSL 54 1'[OPENED_DATE_CST] + 'AOTS Daily CSL 54 1'[Days to Due Date]
                > TODAY (),
            "TBD",
            "0"
        )
    )
)

[surajde]

@johnt75  Bingo!! it worked!! 

Thanks a ton. You just solved my long pending query in a minute.

[johnt75]

Your calculated column would return either text or an integer depending on which condition is met, that's not allowed. Instead of returning 0 or 1 return "0" or "1".

For the second error, you need to check the type of the GreaterThanOpenDue column. If that is a boolean column then you need to remove the " around True in the IF statement.