Forum Discussion
Count comma delimited data
Hi,
I do not have a fancy way to solve this. All i can suggest is that we create two tables fro your source as shown below. I have done so by using the Split column > By rows feature of Power Query.
Hi,
Do not split the data by columns. SPlit it by rows as suggested in my previous post.
| occurrence # | report type | date | route | location |
| 1 | I | 21/08/2017 0:00 | 1 | A |
| 2 | I | 22/08/2017 0:00 | 4 | B |
| 3 | I | 22/08/2017 0:00 | 6 | G |
| 4 | I | 23/08/2017 2:05 | 3 | T |
| 5 | I | 23/08/2017 0:00 | 6 | V |
| 6 | G | 23/08/2017 15:35 | 7 | T |
| 7 | C,F,F | 23/08/2017 13:30 | 9 | T |
| 8 | V,F | 22/08/2017 16:01 | 4 | T |
| 9 | I | 24/08/2017 0:00 | 2 | E |
| 10 | C | 24/08/2017 18:00 | 4 | E |
| 11 | I | 22/08/2017 18:10 | 6 | G |
| 12 | F,F,C,G | 23/08/2017 19:00 | 8 | B |
| 13 | I | 23/08/2017 12:00 | 5 | A |
| 14 | I | 24/08/2017 17:00 | 2 | A |
| 15 | G | 22/08/2017 4:56 | 2 | T |
| 16 | G | 07/08/2017 15:45 | 1 | V |
| 17 | G | 18/08/2017 11:25 | 3 | V |
| 18 | G | 18/08/2017 11:30 | 6 | L |
| 19 | I | 20/08/2017 23:00 | 8 | M |
| 20 | G | 21/08/2017 7:00 | 9 | M |
I cant show the actual data but here is the same idea. I want to be able to have, say, a pie chart which shows the distribution of report type. When I slice the data for location T for example, I want to be able to see what report types are related to location T. I can get this result fine but the problem lies in the rows were there are multiple report types in one occurrence; Power BI does not count those results, it only counts the rows where there is only 1 type. If I split the report type column into multiple columns (type.1, type.2, type.3, etc), the new problem is that Power BI only counts the first column because the other relationships are rendered inactive because they all relate to the same column in table 2.
I should also say that another goal is to simply count the total number of each report type submitted without slicing.
Did you find any solution? I have been trying to solve this exact problem for 1 week already
Hi,
The solution from Ashish is pretty much it. Splitting the delimited data into rows works best. The id number will get duplicated but BI will accurately count each instance of the category.
For me this causes an unwanted duplication of all my other fields so my solutution for this is to split the category information out into a separate table that is linked to the host data table via the ID column.
I'm sure theres a better, more elegant way, but its working for me at this time until I sit down and try to work out a more elegant solution.
Wake
I should add that I do this for each field which contains delimited data.
I then have a measure which uses ROUNTROWS to get the total.
TOTAL = COUNTROWS(tblCategory)