Max of a count measure

[dphillips]

Hi,

I have created a few measures which count the number of demerits for each student. I have added these to a matrix.

DemeritCount = calculate(
        COUNT(pastoral_demerits[id]),
        ALLEXCEPT(uncRedshift_Studentresults,
            uncRedshift_Studentresults[FileYearCurrent],
            uncRedshift_Studentresults[CohortYrLvl]))

DemeritCountStudent = CALCULATE(
        COUNT(pastoral_demerits[id]),
        ALLEXCEPT(uncRedshift_Studentresults,
                    uncRedshift_Studentresults[NameNum],
                    uncRedshift_Studentresults[FileYearCurrent],
                    uncRedshift_Studentresults[CohortYrLvl]))

DemeritMax = 
maxx(SUMMARIZE(pastoral_demerits,pastoral_demerits[student_id]),
    [DemeritCountStudent])

The first two measures seem to work fine. Column 1 - 356 is the total amount of demerits by all students. Column 2 -  a count of the total number of demerits by each student.

In the last measure I am trying to get an expected result of 13 in every row in the matrix (The maximum number of demerits by any student). However, this measure returns the maximum number of demerits for each student which is not want I want. 

I know this must be pretty simple but I just cant seem to get the DAX right to give me 13 for every student. 

Any help would be appreciated. I don't have data at the moment as it will take a while to sanitise and remove unnecessary data and joined tables.

[jdbuchanan71]

Try this

DemeritMax =
CALCULATE (
    MAXX (
        SUMMARIZE ( pastoral_demerits, pastoral_demerits[student_id] ),
        [DemeritCountStudent]
    ),
    ALL ( pastoral_demerits )
)

[dphillips · Accepted solution]

Thanks for your help. I had to adjust this slightly to make sure it only used data from the current FileYear and CohortYrLvl so had to use ALLEXCEPT. The measure below seems to work well. Thanks again!

DemeritMax = 
CALCULATE (
    MAXX (
        SUMMARIZE ( pastoral_demerits, pastoral_demerits[student_id] ),
        [DemeritCountStudent]
    ),
    ALLEXCEPT (uncRedshift_Studentresults,
        uncRedshift_Studentresults[FileYear],
        uncRedshift_Studentresults[CohortYrLvl] )
)

[MattAllington]

Try this

MaxDemeritCountStudent = CALCULATE(
        COUNT(pastoral_demerits[id]),
        ALLEXCEPT(uncRedshift_Studentresults,
              /*      uncRedshift_Studentresults[NameNum], */
                    uncRedshift_Studentresults[FileYearCurrent],
                    uncRedshift_Studentresults[CohortYrLvl]))

[dphillips]

With the NameNum field commented out it gives me 356 for each row - the total number of demerits. This is the same as DemeritCount. With the NameNum field not commented out, I get the count for each student - the same as Column 2 - DemeritCountStudent. I want the maximum for any student - that is, DemeritMax expected result should be 13 for every row.