Using columns of different data types to create a conditional column

Question

I want to create a conditional column using DAX, which compares 2 date columns and a status column that derives a condition.

I have tried this code:

Status of Feedback =
IF (
    ISBLANK ( 'DAT IndAudPlan_IntSys_FindgMgmt_Findgs_RA_5Whys'[ProcConfm_CompdDate] )
        || 'DAT IndAudPlan_IntSys_FindgMgmt_Findgs_RA_5Whys'[DatePlus20] > 'DAT IndAudPlan_IntSys_FindgMgmt_Findgs_RA_5Whys'[Present Day]
            && ISBLANK ( 'DAT IndAudPlan_IntSys_FindgMgmt_Findgs_RA_5Whys'[ProcsAccptce_Status] ),
    "Audit<20days",
    0
)

But I am getting the following error: Expressions that yield variant data-type cannot be used to define calculated columns.

Is there any way I can achieve what I need?

Marcus · Accepted Answer · 2022-11-30 11:22:06Z

You cannot have an IF block that for TRUE yields one data type (e.g. STRING) and for FALSE yields another (e.g. INTEGER) - this is what is referred to in the error as expressions that yield variant data-type. In other words, the problem is not the columns you are using in your condition statement, but what you have assigned to be the output of this function.

Try this:

Status of Feedback =
IF (
    ISBLANK ( 'DAT IndAudPlan_IntSys_FindgMgmt_Findgs_RA_5Whys'[ProcConfm_CompdDate] )
        || 'DAT IndAudPlan_IntSys_FindgMgmt_Findgs_RA_5Whys'[DatePlus20] > 'DAT IndAudPlan_IntSys_FindgMgmt_Findgs_RA_5Whys'[Present Day]
            && ISBLANK ( 'DAT IndAudPlan_IntSys_FindgMgmt_Findgs_RA_5Whys'[ProcsAccptce_Status] ),
    "Audit<20days",
    "0"
)

Or leave out the FALSE block entirely:


Status of Feedback =
IF (
    ISBLANK ( 'DAT IndAudPlan_IntSys_FindgMgmt_Findgs_RA_5Whys'[ProcConfm_CompdDate] )
        || 'DAT IndAudPlan_IntSys_FindgMgmt_Findgs_RA_5Whys'[DatePlus20] > 'DAT IndAudPlan_IntSys_FindgMgmt_Findgs_RA_5Whys'[Present Day]
            && ISBLANK ( 'DAT IndAudPlan_IntSys_FindgMgmt_Findgs_RA_5Whys'[ProcsAccptce_Status] ),
    "Audit<20days"
)

Collectives™ on Stack Overflow

Using columns of different data types to create a conditional column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related