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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Parno
Frequent Visitor

Calculate Column - Try to create an IF and AND statement

Hi although I'm not new new to PowerBI I am struggling trying to create a calculated column.  I have named the column Leaver in the same year.  I am running a data set on new starters - but also interested if they left within the same period, my attempt is below, 

 

Leaver in the same year = IF ((HESA Extract'[JOINER IN PERIOD] ="1"), AND (HESA Extract'[LEAVER IN PERIOD] ="1"),AND (HESA Extract'[TYPE] ="Continuing"),'Yes, No"))
 
Can anyone tell me where I am going wrong?
 
1 ACCEPTED SOLUTION
Samarth_18
Community Champion
Community Champion

@Parno Please try this:-

Leaver in the same year =
IF (
    INT('HESA Extract'[JOINER IN PERIOD]) = 1
        && INT('HESA Extract'[LEAVER IN PERIOD]) = 1
        && 'HESA Extract'[TYPE] = "Continuing",
    "Yes",
    "No"
)

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

View solution in original post

8 REPLIES 8
Samarth_18
Community Champion
Community Champion

Hi @Parno ,

 

You could use below code:-

Leaver in the same year =
IF (
    'HESA Extract'[JOINER IN PERIOD] = "1"
        && 'HESA Extract'[LEAVER IN PERIOD] = "1"
        && 'HESA Extract'[TYPE] = "Continuing",
    "Yes",
    "No"
)

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

Thanks for your help! really appreciated - I get the following message - any ideas?

 

DAX comparison operations do not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

Samarth_18
Community Champion
Community Champion

@Parno Please try this:-

Leaver in the same year =
IF (
    INT('HESA Extract'[JOINER IN PERIOD]) = 1
        && INT('HESA Extract'[LEAVER IN PERIOD]) = 1
        && 'HESA Extract'[TYPE] = "Continuing",
    "Yes",
    "No"
)

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin

Thank you so much - you are amazing! 

 

guibenassi
Advocate II
Advocate II

@Parno take a look if there are a sintax problem.
you can try 1 of 2 of this:
Leaver in the same year = IF ((HESA Extract'[JOINER IN PERIOD] =1 && HESA Extract'[LEAVER IN PERIOD] = 1 && HESA Extract'[TYPE] ="Continuing","Yes", "No"))
Leaver in the same year = IF ((HESA Extract'[JOINER IN PERIOD] ="1" && HESA Extract'[LEAVER IN PERIOD] = 1 && HESA Extract'[TYPE] ="Continuing","Yes", "No"))

Thank you, but of these show the result as #ERROR

 

Idrissshatila
Super User
Super User

Hello @Parno ,

 

try this

 

Leaver in the same year = IF ((HESA Extract'[JOINER IN PERIOD] =1 && HESA Extract'[LEAVER IN PERIOD] = 1 && HESA Extract'[TYPE] ="Continuing",'Yes", "No"))

If I answered your question, please mark my post as solution so it would appeare to others, Appreciate your Kudos 👍

Follow me on Linkedin





Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




Thank you, but sadly this didnt work.

 

Parno_0-1681830901261.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.