Count of multiple values in the same column

[dataanalyst2023]

Good afternoon, I am needing some help figuring out how to calculate the top 3 values answered in a survey. 

Ex the question says as follow: Select your top 3 colors (out of a list of 10 they can only select 3)

Answers: purple; white; blue

               white; yellow; blue

               yellow; pink; black

in my report I want the visual to display the top 3 colors mentioned. In this case, White-2, Blue-2, Yellow- 2

[v-xinruzhu-msft]

Hi,

Thanks for the solution @Greg_Deckler  offered, it is excellent,   and i want to offer user another solution by using dax to refer to.

and i want to offer some more information for user to refer to.

hello @dataanalyst2023 , you can also use dax , you can refer it.

Sample data 

Color table:

Answer table

You can create a measure 

Measure = COUNTROWS(FILTER('Answer',CONTAINSSTRING([Answers],MAX(Color[Color]))))

Then put the color field and the measure to the table visual, and in visual field, set the following filters:

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dataanalyst2023]

Thank you both for all your help. I would rather use DAX withing the same table to not have to create a second source as splitting it will duplicate my responses if I don't create a second source. But @Greg_Deckler solution did give me the answers I want to display.

@v-xinruzhu-msft  using the solution you offered it does work but I still get all 3 in the same cell and not separate. 

This is my column 

The visual gives me the top 3 answers with all 3 colors in the same cell. 

[Greg_Deckler · Accepted solution]

@dataanalyst2023 If you split that column based on ";" and then unpivot those columns this is a cake walk.