Custom Column Help!

[DemandMGR]

I am here once again asking you wonderful folks for assitance. I am having trouble writing an IF or IF(AND) or an IF(AND)ELSE formula in a custome column. Below is where I left off. I was just trying to get the first If statement going but I cant even get that right.

= IF(AND([QOH]= 0,[Reason] = "T",
"MNF OOS"))

My goal is to identify all SKUs that have [QOH] = 0 and have a [Reason] = "T"  be called 'MNF OOS".

Also I would like to  identify all SKUs that [QOH] = 0 and have a [Reason] = "N"  be called DC OOS.

Then I would want all Else be [Item Status].

If there is a way to do all of that in one custome column that would be ideal but I would take them sperately as well. 

Please let me know if you need more information. It also does not have to be an IF(AND) statement. If there is a better way to approach this I am all ears. I am so close to finishing this project and this is my last step.

Thank you in advanced.

[bcdobbs]

I think your intial IF statement is just has the bracket in the wrong place:

= IF(
	AND([QOH]= 0,[Reason] = "T"),
	"MNF OOS")

You can do the whole thing in one step like this:

SWITCH ( 
	TRUE (),
	AND([QOH]= 0,[Reason] = "T"), "MNF OOS",
	AND([QOH]= 0,[Reason] = "N"), "DC OOS",
	BLANK()
)

[DemandMGR]

I tried the SWITCH statement becasue ultimately I would like to get it all in one column and it gave me "The name 'SWITCH' wasn't recognized. Make sure its spelled correctly" error. That is the same problem I was getting when I was writting my original IF statement.

I hate to admit but I do not have any idea why this is happening to how to trouibleshoot it. 

[bcdobbs]

Can you share a screen shot of where you're entering the calculated column code?

[DemandMGR]

I am doing it as a Custom Column. The reason being is becasue I need the column to exist in Power Query so I can combine it with a conditional column I created.

Also wondering if I replace the BLANK() with a [COLUMN} will that still work?

[bcdobbs · Accepted solution]

Oh ok! Power query uses M not dax!

This will get you started:

https://docs.microsoft.com/en-us/powerquery-m/m-spec-conditionals

You don't use brackets. It should look like:

if condition then output else output

all lower case for if, then else.

you can also put a further of statement in the else section.

M had no switch statement.

[DemandMGR]

I had no idea the IF needed to be lower case in Power Query so I really appreciate that. With what you showed me I realized I could do everything I needed to do as and IF ELSE THEN statement.

I started with an IF and then statement and I am getting an error in the first line. Maybe you can help me with that? I am so close now and I  really appreciate all your help with this. You can see the error is under the treu expression