How to get the maximum occurence for a category

[PowerrrBrrr]

I have table like this 

Subject	Status
Math	Pass
Math	Pass
English	Fail
English	Pass
Math	Pass
Hindi	Fail
Hindi	Fail
Math	Fail

I need to list the subject which has maximum "Pass". i.e. here Math should appear in my Visual as it appeard 3 times

[v-rongtiep-msft · Accepted solution]

Hi @PowerrrBrrr ,

Please refer to my pbix file to see if it helps you.

Create two measures.

_count =
CALCULATE (
    COUNT ( 'Table'[Status] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Subject] = SELECTEDVALUE ( 'Table'[Subject] )
            && 'Table'[Status] = "Pass"
    )
)

Measure_2 =
VAR _maxvalue =
    MAXX ( ALL ( 'Table' ), [_count] )
RETURN
    IF (
        [_count] = _maxvalue,
        MAX ( 'Table'[Subject] ) & " " & _maxvalue,
        BLANK ()
    )

If I have misunderstood your meaning, please provide more details with your desired output.

Best Regards
Community Support Team _ Polly

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

[johnt75]

You can create a measure like 

Top subject = 
var summaryTable = ADDCOLUMNS( VALUES('Table'[Subject]), 
    "@num passes", 
    CALCULATE( COUNTROWS('Table'),'Table'[Status] = "Pass")
)
return CONCATENATEX( TOPN(1, summaryTable, [@num passes]), [Subject], ", ")

In the event that there are 2 or more subjects with the same number of passes this will return a comma separated list of all of them

[PowerrrBrrr]

What is @num passes for. I tried and i get error as Column 'Subject' cannot be found or may not be used in this expression

[johnt75]

[@num passes] is used to rank the summary table, so that you can get the top one. If you've changed the 'Table'[Subject] to match the proper table and column name from your model then just change the [Subject] column in the CONCATENATEX to match the column name from your model.