Forum Discussion
sharmisaranga09
Helper I
Helper IFind maximum occurred item for each category
Hi,
I have a question and answer columns in my table, I want to find the count of unique answers for each questions and get the maximum count answer for each question
Expected result is, find maximum answered Answer text for each question
sharmisaranga09 The measure form is:
Max count Answered (measure) = VAR __Question = MAX( [Question] ) VAR __Table = SUMMARIZE( FILTER( ALL( 'Table' ), [Question] = __Question ), [Answer], "__Count", COUNTROWS( 'Table' ) ) VAR __Max = MAXX( __Table, [__Count] ) VAR __Result = MAXX( FILTER( __Table, [__Count] = __Max ), [Answer] ) RETURN __Result
4 Replies
- Kaviraj11
Solution Sage
Hi,
You can achieved this in Power Query by doing a Group by:
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
- Greg_Deckler
Community Champion
sharmisaranga09 Try this:
Max count Answered (column) = VAR __Question = [Question] VAR __Table = SUMMARIZE( FILTER( 'Table', [Question] = __Question ), [Answer], "__Count", COUNTROWS( 'Table' ) ) VAR __Max = MAXX( __Table, [__Count] ) VAR __Result = MAXX( FILTER( __Table, [__Count] = __Max ), [Answer] ) RETURN __Result 
sharmisaranga09The count/frequency column i mentioned earlier was an output from a measure
- Greg_Deckler
sharmisaranga09 The measure form is:
Max count Answered (measure) = VAR __Question = MAX( [Question] ) VAR __Table = SUMMARIZE( FILTER( ALL( 'Table' ), [Question] = __Question ), [Answer], "__Count", COUNTROWS( 'Table' ) ) VAR __Max = MAXX( __Table, [__Count] ) VAR __Result = MAXX( FILTER( __Table, [__Count] = __Max ), [Answer] ) RETURN __Result
