I have created an Excel table of survey data. Many of the questions are categorical multi-select questions (e.g. what category of product did you buy? and the respondent can select multiple devices). I unpivoted the multi-select data in Power Query and set up relationships between each table and the main demographics table using respondent ID# as the key. Some filters don't seem to work.
When I create the pivot table of purchases by category, I would like to see which categories were purchased in different demographics groups. That pivot works. I can then filter that by gender, which also works. The gender source data has a unique gender allocation per respondent. If I try to filter the segment/category table by PMT method that filter doesn't work. Each respondent in the dataset can choose multiple payment methods PMT method.
The attached pivot table shows the example that I have described.
Any ideas on why the filters of non-unique data would not work? Suggestions or solutions would be most welcome because I am wasting so much time on this, something that I thought would be quite easy to implement.
Bookmarks