Hi Paul, this seems like a you have your data concatenated in a string format but want to treat them as separate values. GoodData will not be able to split it for you in a metric. But if you slightly modify your data model and split the data in your ETL (or a view) , you can hen use the native support for the many-to-many relation in GoodData like this:
You can set the many-to-many relationship in the modeller by dragging the arrow in the model both ways. There are some rules and limitations but your case with “reasons” seems to be exactly the one for which the many-to-many (or M:N) relationships are intended.
I do not know how exactly your current data model look like, but a simple model with customers, sales and purchase reasons (where each sales transaction can have multiple purchase reasons) would look like on the attached image.
here the contents of the main tables would look like this (simplified):
Sales
purchase ID, Product, Customer, Revenue
ID1, Product A, Customer X, $100
ID2, Product A, Customer X, $200
ID3, Product B, Customer Y, $1500
Purchase Reasons:
Purchase Reason
price
design
quality
Sales Purchase Reasons
Purchase ID, Purchase Reason
ID1, price
ID1, design
ID1, quality
ID2, price
ID3, design
ID3, quality
Then with this setup you would be able to view (or filter) your sales (counts, amounts…) by the “Purchase Reason” dimension and it would work.
NOTE: the first arrow goes from “Sales” to “Sales Purchase reasons” (so Sales Purchase reasons has a foreign key pointing to to Sales in it) and after that you just drag the arrow also the other way (no other foreign key is added).
NOTE 2: please only use the M:N relation where it really makes sense - where there is a multi-value relationship, do not try to put them everywhere “just in case”
Here is a documentation (for GD Platform) how it works and how to set it up:
https://help.gooddata.com/doc/enterprise/en/data-integration/data-modeling-in-gooddata/many-to-many-in-logical-data-models/
NOTE 3: If you would have huge number of rows in Sales (and therefore also in Sales Purchase Reasons) tables and the join would take too much time, you can also consider this a bit more complex approach which reduces the number of rows needed:
https://help.gooddata.com/doc/enterprise/en/data-integration/data-modeling-in-gooddat[…]ny-in-logical-data-models/many-to-many-modeling-performance/