How to group by a multi-select column so that each group is based on unique items instead of unique combinations of items.
When you group a table by a select list column or a relation column, and you also have the Allow multiple selections option enabled on that column, you'll notice that each group is defined by unique combinations of items, rather than each unique item.
Let's use an example. Imagine you have a doc for organizing your favorite movies. You've added multiple genre tags on each movie:
You then want to see all the movies for each genre tag. When you group by the Genres column, each group is based on a unique combination of tags, not the individual tags themselves:
You'll notice that the drama tag is repeated twice in combination with another tag. This is useful for a lot of scenarios, but in your case, you'd like to see each tag as a separate group.
To do this, you'll want to follow 2 steps:
Step 1: Convert your select list column type into a relation column type, and create a new Genres table.
Before you do any grouping, navigate to the column type icon on the Genres column, and select Edit column from the menu. At the bottom of your list of items, click Convert to table.
Step 2: Add a linked relation column to the new Genres table, to see all the movies that contain that given tag.
Add a column to your newly created Genres table. When prompted to choose a column type, select Linked relation. You should automatically see a suggested link to your Movies table. Select this suggestion.
Now you can see each genre as its own row, with the associated movies for each genre.
💡 Prefer to see the movies as a bulleted list, rather than as "bubbles" of row references?
You can set this up with a formula. Instead of a linked relation column, add a new formula column to the table. Check out the example formula below:
Let's break this formula down. We're filtering the movies table ("My favorite movies") with Contains() to see where each Genre value exists in that movies table. From there, we're using the dot operator to pull out the text title of the movie (the "Movie name" column). And to clean up the list, we added BulletedList() at the end.