Help creating Custom calculated column with if then else statement.

[audi357]

I'm new to Power BI this statement works in Alteryx but I trying to convert over to Power BI so any assistance helps.

Here is a smaller sample of what I am asking:

IF [ucr_code] >= 3940 AND [ucr_code] <= 3999 THEN "INTIMIDATION"

ELSEIF [ucr_code] >= 4200 AND [ucr_code] <= 4299 THEN "KIDNAPPING"

ELSEIF [ucr_code] >= 4315 AND [ucr_code] <= 4330 THEN "THREAT-TERRORISM"

ELSEIF [ucr_code] IN (4505, 4515, 4525, 4526, 4530, 4550, 4570) THEN "VIOLATION OF CRIMINAL REGISTRY LAWS"

ELSEIF [ucr_code] IN (4310, 4387, 4388, 4389, 4410, 4420, 4510, 4580, 4625, 4710, 4720, 4730, 4740, 4750, 4751, 4775, 4800, 4810, 4870, 5000, 5060, 5081, 5082, 5083, 6053, 8179) THEN "OTHER OFFENSES"


ELSEIF [ucr_code] = 8180 THEN "DECEPTIVE PRACTICE"

ELSEIF [ucr_code] = 8182 THEN "DECEPTIVE PRACTICE"


ELSEIF [ucr_code] IN (6605, 8200, 8222, 8223, 8226, 8376, 8158) THEN "MOTOR VEHICLE OFFENSES"

ELSE "Other"

[v-jiascu-msft · Accepted solution]

Hi @audi357,

The function SWITCH could make this simple. 

Column = switch(true(), 
[ucr_code] >= 3940 && [ucr_code] <= 3999 , "INTIMIDATION",
[ucr_code] >= 4200 && [ucr_code] <= 4299 , "KIDNAPPING",
[ucr_code] >= 4315 && [ucr_code] <= 4330 , "THREAT-TERRORISM",
[ucr_code] IN {4505, 4515, 4525, 4526, 4530, 4550, 4570} , "VIOLATION OF CRIMINAL REGISTRY LAWS",
[ucr_code] IN {4310, 4387, 4388, 4389, 4410, 4420, 4510, 4580, 4625, 4710, 4720, 4730, 4740, 4750, 4751, 4775, 4800, 4810, 4870, 5000, 5060, 5081, 5082, 5083, 6053, 8179} , "OTHER OFFENSES",
[ucr_code] = 8180 , "DECEPTIVE PRACTICE",
[ucr_code] = 8182 , "DECEPTIVE PRACTICE",
[ucr_code] IN {6605, 8200, 8222, 8223, 8226, 8376, 8158} , "MOTOR VEHICLE OFFENSES",
"Other")

The IF function can also be a solution.

Column 2 = if( 
[ucr_code] >= 3940 && [ucr_code] <= 3999 , "INTIMIDATION",if(
[ucr_code] >= 4200 && [ucr_code] <= 4299 , "KIDNAPPING", if(
[ucr_code] >= 4315 && [ucr_code] <= 4330 , "THREAT-TERRORISM",if(
[ucr_code] IN {4505, 4515, 4525, 4526, 4530, 4550, 4570}, "VIOLATION OF CRIMINAL REGISTRY LAWS", if(
[ucr_code] IN {4310, 4387, 4388, 4389, 4410, 4420, 4510, 4580, 4625, 4710, 4720, 4730, 4740, 4750, 4751, 4775, 4800, 4810, 4870, 5000, 5060, 5081, 5082, 5083, 6053, 8179}, "OTHER OFFENSES",if(
[ucr_code] = 8180 , "DECEPTIVE PRACTICE",if(
[ucr_code] = 8182 , "DECEPTIVE PRACTICE",if(
[ucr_code] IN {6605, 8200, 8222, 8223, 8226, 8376, 8158} , "MOTOR VEHICLE OFFENSES",
"Other"))))))))

Best Regards,

Dale

[audi357]

Thank you, it worked perfectly.