Forum Discussion
Post PartisanMeasure / DAX to obtain a count
Hi, I need to get the count of the individual incident types when there are multiple selections per incident. For example, using the table below, I need to count the types like this:
ASB - 5
Discrimination - 1
Fire - 2
Medical Concern - 3
Theft - 3
| Incident | Incident_Type | Date |
| 1 | ASB, Discrimination | 2024 |
| 2 | ASB | 2024 |
| 3 | Fire, Theft | 2024 |
| 4 | Medical Concern | 2024 |
| 5 | Theft, ASB, Fire | 2024 |
| 6 | Medical Concern, ASB | 2024 |
| 7 | ASB, Theft | 2024 |
| 8 | Medical Concern | 2024 |
Thanks
RichOB Go to the Power Query Editor by clicking on Transform Data.
Select the Incident_Type column.
Go to the Home tab, then click on Split Column > By Delimiter.
Choose Comma as the delimiter and select Split into Rows.After splitting the column, you will have a new table where each incident type is in its own row.
Rename the new column to Incident_Type.Create a new measure to count the occurrences of each incident type.
IncidentTypeCount = COUNTROWS('YourTableName')
Use a Table or Matrix visualization.
Add the Incident_Type column to the rows.
Add the IncidentTypeCount measure to the values.
2 Replies
- bhanu_gautam
Super User
RichOB Go to the Power Query Editor by clicking on Transform Data.
Select the Incident_Type column.
Go to the Home tab, then click on Split Column > By Delimiter.
Choose Comma as the delimiter and select Split into Rows.After splitting the column, you will have a new table where each incident type is in its own row.
Rename the new column to Incident_Type.Create a new measure to count the occurrences of each incident type.
IncidentTypeCount = COUNTROWS('YourTableName')
Use a Table or Matrix visualization.
Add the Incident_Type column to the rows.
Add the IncidentTypeCount measure to the values.
