Forum Discussion
Count comma delimited data
I have used power query to split the column into several columns so now i have columns: `type.1` `type.2``type.3` etc. in table 1. These columns all relate to the column `Type` in table 2. However, when i create relationships from each of the columns in table 1 to column 2 only the first one I create is active and the remainder are inactive. As a result, power BI is only counting the active relationship in its output. How can i make it count all of them as one statistic?
In addition to the answer above, I would still prefer to leave this column unsplit and somehow count each report entered for one occurrence. for the most part there will only be 3 - 4 report types per occurrence but on the rare occasion there could be upwards of 20 reports for one occurrence. Obviously having 20 columns just for the off chance that this occurs is not ideal but if I limit the amount of columns the origin column is split in to say 10 then I am possibly loosing out on data.
Super UserHi,
Please show your actual data and your expected result.
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
- Ashish_Mathur
Hi,
Do not split the data by columns. SPlit it by rows as suggested in my previous post.
but then the data will no longer be 'per occurrence'