Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 Group | Incident Code |
1 - Fire | 13 |
2 - Overpressure | 130 |
3 - EMS | 131 |
4 - Hazmat | 132 |
5 - Service | 133 |
6 - Good Intent | 134 |
7 - False | 135 |
8 - Severe Weather | 136 |
9 - Special Incident | 137 |
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
@Anonymous ,
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"))))))
This block gets the first number and can be used for the others values:
MID('Table (2)'[Incident Code], 1, 1
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |