In list filter on comma seperated list
we have a asset database with a field "genre" storing a comma separated list of genres for each asset. This are also connected to purchases. How would you guys best filter on genre (more than one genre possible), if you as well need to sum up the revenue?
What I tried so far:
- Building a relation table for genre and asset id which gives me the opportunity to create in list filter, but in that case the sum of the revenue is multiplied with the number of genre (purchase x number of genres)
- Building a Filter using a SQL Query to get the distinct values of all the comma separated lists. In that case I don´t need to create a relation table and have a distinct filter list of possible genres and the revenue is summed up correctly, which is perfect, but I am not able to select more than one genre.