Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jaltoft
Resolver I
Resolver I

Formulae help

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"

 

1 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")

View solution in original post

2 REPLIES 2
timg
Solution Sage
Solution Sage

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?

1.PNG

Regards,

Tim





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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")

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.