Forum Discussion
Solved
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 [u...
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
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.