Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dphillips
Helper IV
Helper IV

Max of a count measure

Hi,

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

Demerit Max.png

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.

1 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] )
)

View solution in original post

4 REPLIES 4
jdbuchanan71
Super User
Super User

Try this

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

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] )
)

Try this

 

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


* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.