Creating a Report using a multi-line text
How do I create a report to determine to distinct count from two columns: (i) a unique ID column and (ii) multi-text column (with a ; delimiter)
For example
| User ID | Colour Preferences |
| 1001 | Blue; Black |
| 1002 | Black; Red |
I want to create a chart that counts the distinct number of colour preferences (i.e. Black is 2)