Forum Discussion
Count of multiple values in the same column
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
dataanalyst2023 If you split that column based on ";" and then unpivot those columns this is a cake walk.
3 Replies
dataanalyst2023 If you split that column based on ";" and then unpivot those columns this is a cake walk.
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.
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.
Anonymous 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.
