IN function

[hlin]

Hi all,

I added a new column using the following function. However it didn't work, could you please help?

Work Category = if([NT Coding]="D/CUSTSERV/CSTD","Standard",

if(AND( ('BO - Notifications'[WO PM Activity Type]="New Connection <160 amps (Resi"),('BO - Notifications'[YearMonthINTQuoteRequest]<201704)), "Standard",

if( 'BO - Notifications'[WO PM Activity Type] IN {"New Connection >=160 amps (com", “Other Quotable Works","Relocation","Street Lightling","Subdivisions",“Undergrounding”},"Non Standard", blank()))

Regards,

Ling

[Anonymous]

Looks like you have placed a ) in the wrong spot.  That itself will give you problems.  In your AND statement you have two statements, the first one is missing its bracket.  It needs to look like this:

('BO - Notifications'[WO PM Activity Type] = "New Connection <160 amps (Resi"))

That of course means you have an extra bracket elsewhere, which I believe I've fixed.  I've copied your code into an editor such as Notepad++ which made it a lot easier to locate the bracket problem.  Here is the formatting fixed version:

Work Category = if([
	NT Coding]="D/CUSTSERV/CSTD","Standard", 
	if(
		AND(
			('BO - Notifications'[WO PM Activity Type] = "New Connection <160 amps (Resi")),
			('BO - Notifications'[YearMonthINTQuoteRequest] < 201704)
		), 
		"Standard"
	),
	if(
		'BO - Notifications'[WO PM Activity Type] IN {
			"New Connection >=160 amps (com", 
			“Other Quotable Works","Relocation",
			"Street Lightling",
			"Subdivisions",
			“Undergrounding”
		},
		"Non Standard", 
		blank()
	)
)

[hlin]

Thank you for your prompt response. I copy your solution into my file, it didn't work.

first error mesage was incorrect "," position, when I deleted the last ")" in the statement below, the error message disappeared.

('BO - Notifications'[WO PM Activity Type] = "New Connection <160 amps (Resi")),

however, it shows below error message:

The following syntax error occurred during parsing: Invalid token, Line 12, Offset 4, “.

[Anonymous · Accepted solution]

I've had a chance to look through your code and get my head around the IN statement that you have tried to include.  From what I can tell, IN is used as a filter operator, however you seem to be wanting to use it like a SWITCH statement.  The ( in your strings through me for a spin as well.

Is this the code you were trying to attempt?

Work Category = if(
	[NT Coding]="D/CUSTSERV/CSTD",
	"Standard", 
	if(
		AND(
			('BO - Notifications'[WO PM Activity Type] = "New Connection <160 amps (Resi"),
			('BO - Notifications'[YearMonthINTQuoteRequest] < 201704)
		), 
		"Standard",		
		SWITCH('BO - Notifications'[WO PM Activity Type], 
			"New Connection >=160 amps (com", "Non Standard", 
			"Other Quotable Works", "Non Standard",
	                "Relocation", "Non Standard",
			"Street Lightling", "Non Standard",
			"Subdivisions", "Non Standard",
        	        "Undergrounding", "Non Standard",
			BLANK()            
		)
	)
)

[hlin]

Thanks a lot! it works now. I am new to Power BI. I really appreciate your help.