Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a small dataset with a column of questions, "Who is most likely to succeed?", "Who is Most likely to be a spy", etc. Then a column with employee responses. I am trying to do a calculated measure so I can see which name got the most votes for each question. I am able to get the number of answers for each question but I want to have a column that shows who got the most votes.
Question | Answer |
Who is most likely to Accidently put their shirt on backwards? | Steve |
Who is most likely to Accidently put their shirt on backwards? | Dave |
Who is most likely to Accidently put their shirt on backwards? | Mike |
Who is most likely to Accidently put their shirt on backwards? | Mike |
Who is most likely to Accidently put their shirt on backwards? | Mike |
Who is most likely to Accidently walk into a wall? | Joe |
Who is most likely to Accidently walk into a wall? | Larry |
Who is most likely to Accidently walk into a wall? | Larry |
Who is most likely to Accidently walk into a wall? | Bill |
So Question 1 next column would be Mike
2 would be Larry
Can anyone point me in the right direction?
Solved! Go to Solution.
@oneillp111 Try:
Measure =
VAR __Table = SUMMARIZE('Table',[Answer],"__Count",COUNTROWS('Table')
VAR __Max = MAXX(__Table,[__Count])
RETURN
MAXX(FILTER(__Table,[__Count] = __Max),[Answer])
or
Measure =
VAR __Table = SUMMARIZE('Table',[Answer],"__Count",COUNTROWS('Table')
VAR __Max = MAXX(__Table,[__Count])
RETURN
CONCATENATEX(FILTER(__Table,[__Count] = __Max),[Answer],", ")
@oneillp111 Try:
Measure =
VAR __Table = SUMMARIZE('Table',[Answer],"__Count",COUNTROWS('Table')
VAR __Max = MAXX(__Table,[__Count])
RETURN
MAXX(FILTER(__Table,[__Count] = __Max),[Answer])
or
Measure =
VAR __Table = SUMMARIZE('Table',[Answer],"__Count",COUNTROWS('Table')
VAR __Max = MAXX(__Table,[__Count])
RETURN
CONCATENATEX(FILTER(__Table,[__Count] = __Max),[Answer],", ")
thanks @Greg_Deckler , can you clarify somethings for me, what is "___Count" in line 2
@oneillp111 Just a variable name. I always prefix my variable names with a double underscore for whatever reason (mainly to prevent misidentification with columns and measures and such)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |