Measure returning MAX Values from Child tables

Question

HelloI’ve got the measure below , that counts the number of students based on filtering their Class Type and Exam Status. ([ClassType] = "Audit"&nbsp;&nbsp;&amp;&amp;[ExamStatus] = "Resit")&nbsp;&nbsp;Students can have multiple Class Types and Exam Status, but the measure counts the latest (max) Exam Date.EvaluateRow("a",&nbsp;&nbsp;CALCULATE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;COUNTX (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ADDCOLUMNS (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUMMARIZE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;StudentHistory,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;StudentHistory[StudentID],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;StudentHistory[ClassType],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"MaxDate", MAX ( StudentHistory[ExamDate] )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"ExamStatus", LOOKUPVALUE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;StudentHistory[Exam_Status],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;StudentHistory[StudentID], [StudentID],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;StudentHistory[ExamDate], [MaxDate]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;StudentHistory[ClassType] = "Audit"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;[ExamStatus] = "Resit"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;StudentHistory[StudentID]&nbsp;&nbsp;) ))For each Class Types and Exam Status they have a tutor that may change , as follows&nbsp;&nbsp;Even though the numbers returned by the measure is correct, If I add the tutor or the Exam date etc to a report&nbsp;&nbsp;, the Measure number remains the same, but the&nbsp;report surfaces all Students that meet the criteria of ([ClassType] = "Audit"&nbsp;&nbsp;&amp;&amp;[ExamStatus] = "Resit")&nbsp;&nbsp;regardless of it being&nbsp; the latest date or not (the total count is still correct)My question is, what do I have to do to ensure the only the latest Tutor, Exam Date etc are surfaced in a report? So I don’t for example get 20 students in the report, and the measure is returning a (correct) count of 16Thanks in advance&nbsp;Roy

stachu · Answer

the MAX date that you speak of is specific to student id, correct? that is you want to:
1) filter the dates to the latest exam for a given student
2) filter the result to Audit and Resit rows
3) count the student ids
so the measure result based on the screenshot you attached would be 2 for students 100 and 103 as only they have the latest [ExamStatus]="Resit"
&nbsp;
do I understand the requirement correctly?EDIT - adjusted the numbers to match the dates order

stachu · Answer

try this measure:
Measure =
VAR __AuditOnly =
    FILTER ( 'StudentHistory', 'StudentHistory'[ClassType] = "Audit" )
VAR __MaxDates =
    GROUPBY (
        __AuditOnly,
        StudentHistory[StudentID],
        StudentHistory[ClassType],
        "ExamDate", MAXX ( CURRENTGROUP (), 'StudentHistory'[ExamDate] )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( StudentHistory[StudentID] ),
        TREATAS (
            __MaxDates,
            StudentHistory[StudentID],
            StudentHistory[ClassType],
            StudentHistory[ExamDate]
        ),
        'StudentHistory'[Exam_Status] = "Resit"
    )

anonymous · Answer

Thank you for Getting back to me.&nbsp;Points 1, 2 and 3 are correct.&nbsp;The issue is my measure is counting the correctly, in this case 2, but when I pull out other data into a pivot, e.g. Exam date,&nbsp; I end up with all 4 Students, becaue they all&nbsp; took Resits at some stage.&nbsp;&nbsp;All I want is the Exam Date for 2 students&nbsp;&nbsp;I should have mentioned in my original post, our tabular cube does not support DAX Var?&nbsp;

anonymous · Answer

Thanks for the reply,&nbsp;I should have mentioned in my original post, our tabular cube doesn't support DAX Var?

stachu · Answer

this is the code without variables
Measure =
CALCULATE (
    DISTINCTCOUNT ( StudentHistory[StudentID] ),
    TREATAS (
        GROUPBY (
            FILTER ( 'StudentHistory', 'StudentHistory'[ClassType] = "Audit" ),
            StudentHistory[StudentID],
            StudentHistory[ClassType],
            "ExamDate", MAXX ( CURRENTGROUP (), 'StudentHistory'[ExamDate] )
        ),
        StudentHistory[StudentID],
        StudentHistory[ClassType],
        StudentHistory[ExamDate]
    ),
    'StudentHistory'[Exam_Status] = "Resit"
)

Forum Discussion

Measure returning MAX Values from Child tables

6 Replies

Related content

Measure returning incorrect value

Create Measure to Return Highest Value

Measure to return multiple values

SUMX(VALUES(column), [Measure]) returns parent-level value, while ADDCOLUMNS/SUMMARIZECOLUMNS works

Date Different return wrong value