Nested IF with AND and OR

[tbennett]

I'm pretty new to PowerBI and I've hit a bit of a stumbling block when it comes to nested if's with AND and OR's.  I've had a good look at previous posts and I'm even more confused as I've seen comments about using the SWITCH function instead, so not sure which I should be using.

I want to create a new column that is populated based on the data in two different columns.

If column 1 contains "Pass" or "Intermediate Award" or "Proceed Carry" AND column 2 = "Did not return" then return the value of "Eligible to Return".

This is part of a more longer IF statement, but the rest is based on one column of data.  So far I have:

Progression = if(and(Sheet1[FY_COURSE_LEVEL_CODE]="UGN",Sheet1[COURSE_LEVEL_CODE]="UGD"),"Did not Progress to Higher Level",if(Sheet1[FY_MODE_OF_STUDY_CODE]="PART_TIME","Did not Progress to Higher Level",if(Sheet1[FY_YEAR_OF_COURSE]<=Sheet1[YEAR_OF_COURSE],"Did not Progress to Higher Level",**,"")))

I need to add the if with OR and AND into this statement **

Is there a simple way of doing this?  Any help would be very much appreciated.

[MFelix · Accepted solution]

Hi @tbennett,

You should take a look at the SWITCH statment (link) to do your IF instead of doing nested IF.

Regarding the AND you should use &&  for the OR you should use the || as divider.

Regards,

MFelix

[tbennett]

Many thanks both for you prompt responses! 

[Greg_Deckler]

Without sample data, your IF is going to look something like:

IF (
	AND (
		OR (
			OR (
				SEARCH("Pass",[Column 1],1,0),
				SEARCH("Intermediate Award",[Column 1],1,0)
			   ),
			SEARCH("Proceed Carry",[Column 1],1,0)
		   ),
		[Column 2] = "Did not return"
	    ),
        "Eligible to Return",
        "Not Eligible"
    )