Formulae help

[jaltoft]

Hello,

Can anyone help with my formulae?

= IF('Visit timescale'[Custom category]="Statutory Cases :"||'Visit timescale'[Custom category]="PF"||'Visit timescale'[Custom category]="Contact",AND('Visit timescale'[Eligible for Visit ]=BLANK(),AND('Visit timescale'[Custom category]="CIN",'Visit timescale'[Date of last Visit]="Not Eligible")),"Not Required")

The column Custom category is a text category which pulls through the category of the case.

Eligible for visit is a column that is text which has a Y in if eligible, date of last visit will either be the date or say not required.

I am getting a message Expressions that yield variant data - type cannot be used to define calculated columns.

My issue is ive split the formulae up and in works in bits, the issue seems to come when combing the or and the And.

If trying to replicate this formulae -

IF(OR([@[New Category]]="Statutory Cases:",[@[New Category]]="PF",[@[New Category]]="Contact",AND([@[Eligible for Visit]]="",[@[New Category]]="CIN",[@[Date of Last CIN Visit ]]="Not Eligible")),"Not Required"

[timg]

Hi Jaltoft,

Currently the circled part of the formula is seen as the resultiftrue reaction. So if the first 3 lines are true it will try to execute those next 5 lines. If not true, then it will return "not required". Since the resultistrue part starts with an "AND" this is probably the cause of your trouble. Am I correct to assume that those "AND" statements should also be in the logicaltest argument of the formula instead of the resultiftrue argument?

Regards,

Tim

[jaltoft · Accepted solution]

Hello @timg thanks for the reply I have figured it out -

= IF(OR('Visit timescale'[Custom category]="Statutory Cases:"||'Visit timescale'[Custom category]="PF"||'Visit timescale'[Custom category]="Contact",AND('Visit timescale'[Eligible for Visit]=BLANK(),'Visit timescale'[Custom category]="CIN")&&'Visit timescale'[Date of last Visit]="Not Eligible"),"Not Required")