I am fairly inexperienced in BI and am looking to create a single filter visual that contains each of the below food items that can be found in a Cart. The goal is to be able to see the total occurrence of specific food items across all carts and also see which cart contains which items. (How many carts have apples? Which carts have grapes?) The amount of a specific food item in a single cart is not needed, nor the various kinds of one item matter (apple_large vs apple_small), only the occurrence of it.
The problem I'm running into is there are multiple food items per cart and sometimes an item is duplicated across both Groceries columns or has a slight variance to it (apple_small vs apple_large). I've tried using their delimiter to separate them into more Groceries columns, but that leaves me still with duplicates and variances
| Carts | Groceries 1 | Groceries 2 |
| 1 | apple_small, banana, carrot | avocado, apple_large |
| 2 | grape, avocado_large | banana, apple, avocado_small |
| 3 | avocado | grape, carrot |
| 4 | banana, apple_large | apple |
| 5 | carrot, banana | grape, carrot, avacado |
| | | |
