Forum Discussion
Help creating Custom calculated column with if then else statement.
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"
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
2 Replies
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
Thank you, it worked perfectly.
