Rolling up counts of concatenated data

[erb]

I have a data set capturing risks from 4 various groups, that could affet none to many of those same groups.  I am capturing the "affected" information as a multichoice checkbox and concatenating the data (example data below)

Line item	owner	affects
risk 1	group 1	group 2; group 3
risk 2	group 2
risk 3	group 1	group 2; group 3; group 4
risk 4	group 3
risk 5	group 4	group 2

I would like to add a pie chart to a dashboard that has the slices of the pie as "Group 1 - Group 4" with the value as "count of ID (line item) of occurance in "affects" column".  If I select group 3 in the pie chart, then I should see a full group 3 pie slice, and a partially full group 2 (2/3 full) with the above data set because of their relationship. 

Any ideas how to accomplish this?

FYI - my true number of groups is 13, using 4 as examplatory.

Thanks!

[dramus]

We covered something very similar yesterday. Check out this thread.

Split and unpivot your data.

[erb]

So I split the column into rows using the delimeter, and that wokred well.  But now i have an error:

"column 'Id; in table 'risk & issues' contains dulicate value '10' and this is not allowed for columns on the one side of a many-to-one relationship or for oclumns that are used as the primary key table"

any thoughts on resolving this scenario?

[dramus]

I would expect there to be duplicates in that column. Check that the relationship is set up correctly.

[v-huizhn-msft]

Hi @erb,

There is no 10 your sample table, please share more details for further analysis.

Best Regards,
Angelia