cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

New Calculated Column using IF statement or SWITCH

Hello everyone, 

 

I have a question as to how to create a new calculcated column, based on the values of 2 columns. I'm really new to PowerBI, and to posting on this forum, so I don't know how to attach a sample dataset. I'll try to describe as best as possible. 

 

I have 2 columns, one is Incident Type Group, and the other is Incident Type Code. There are many values for Incident Type code, and I condense it into a group depending on what number the code starts with. If the code started with a 1, it belonged to the 'Fire' group, if it started with a 3, it belonged to the 'EMS' group. Please see 'Incident Group' for more info. 

 

Incident GroupIncident Code
1 - Fire13
2 - Overpressure130
3 - EMS131
4 - Hazmat132
5 - Service133
6 - Good Intent134
7 - False135
8 - Severe Weather136
9 - Special Incident137
 138
 322
 323
 324
 352

 

I would like to create a new column, named 'Monthly Incident Type', where I take into consideration the specific values above in the Incident Code.

If Incident Code is in ['13','130','131','132','133','134','135','136','137', '138'] then it equals 'Vehicle Fire'

If Incident Code is in ['322','323','324','352'] then it equals 'Vehicle Accident'

else, if Incident Code starts with 1, then 'Fire', 

2, then 'Overpressure', 

3, then 'EMS', 

4, then 'Hazamt' and so on based on the Incident Group.  Both Incident Group and Incident Code columns are type Text. 

 

Would anyone know how to approach this? 

 

Thank you for the help, 

 

Denisse

1 REPLY 1
Highlighted
Super User III
Super User III

@ruesaintdenis ,

 

I didn't get the second block for the if, but with this code you can create another column if necessary:

Monthly Incident Type = 
IF( 'Table (2)'[Incident Code] in {13, 130, 131, 132, 133,134,135,136,137,138}, "Vehicle Fire",
IF('Table (2)'[Incident Code] in {322, 323, 324,352}, "Vehicle Accident",
IF( MID('Table (2)'[Incident Code], 1, 1) = "1", "Fire",
IF(MID('Table (2)'[Incident Code], 1, 1) = "2", "OverPressure",
IF(MID('Table (2)'[Incident Code], 1, 1) = "3", "EMS",
IF(MID('Table (2)'[Incident Code], 1, 1)= "4", "Hazamt"))))))

 

Capture.PNG

 

This block gets the first number and can be used for the others values:

MID('Table (2)'[Incident Code], 1, 1

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors