Measure returning MAX Values from Child tables

[Anonymous]

Hello

I’ve got the measure below , that counts the number of students based on filtering their Class Type and Exam Status. ([ClassType] = "Audit"  &&[ExamStatus] = "Resit")  Students can have multiple Class Types and Exam Status, but the measure counts the latest (max) Exam Date.

Evaluate

Row("a",  CALCULATE (

        COUNTX (

            FILTER (

                ADDCOLUMNS (

                    SUMMARIZE (

                        StudentHistory,

                        StudentHistory[StudentID],

                        StudentHistory[ClassType],

                        "MaxDate", MAX ( StudentHistory[ExamDate] )

                    ),

                    "ExamStatus", LOOKUPVALUE (

                        StudentHistory[Exam_Status],

                        StudentHistory[StudentID], [StudentID],

                        StudentHistory[ExamDate], [MaxDate]

                    )

                ),

                StudentHistory[ClassType] = "Audit"

                    &&[ExamStatus] = "Resit"

            ),             StudentHistory[StudentID]  ) ))

For each Class Types and Exam Status they have a tutor that may change , as follows

Even though the numbers returned by the measure is correct, If I add the tutor or the Exam date etc to a report  , the Measure number remains the same, but the report surfaces all Students that meet the criteria of ([ClassType] = "Audit"  &&[ExamStatus] = "Resit")  regardless of it being  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 16

Thanks in advance 

Roy

[Stachu]

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]

Thanks for the reply,

I should have mentioned in my original post, our tabular cube doesn't support DAX Var?

[Stachu]

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

[Anonymous]

Stachu

Thanks for the prompt reply,

I can see the pattern makes sense, but unfortunately our Cube also doesn’t support

TREATAS, GROUPBY or CURRENTGROUP … it’s really old

I’ve tried various things including Using LOOKUP to return the tutor in the measure, but this doesn’t work either, I’m stuck as to what to do now, Nested separate measures for each part?

EVALUATE

ROW (

    "a", CALCULATE (

        COUNTX (

            FILTER (

                ADDCOLUMNS (

                    SUMMARIZE (

                        StudentHistory,

                        StudentHistory[StudentID],

                        StudentHistory[ClassType],

                        "MaxDate", MAX ( StudentHistory[ExamDate] )

                    ),

                    "ExamStatus", LOOKUPVALUE (

                        StudentHistory[Exam_Status],

                        StudentHistory[StudentID], [StudentID],

                        StudentHistory[ExamDate], [MaxDate]

                    ),

                    "Tutor", LOOKUPVALUE (

                        StudentHistory[Tutor],

                                             StudentHistory[StudentID], [StudentID],

                        StudentHistory[ExamDate], [MaxDate]

                    )

                ),

                StudentHistory[ClassType] = "Audit"

                    && [ExamStatus] = "Resit"

            ),            StudentHistory[StudentID]        )))

[Stachu]

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"

do I understand the requirement correctly?

EDIT - adjusted the numbers to match the dates order

[Anonymous]

Thank you for Getting back to me.

Points 1, 2 and 3 are correct.

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,  I end up with all 4 Students, becaue they all  took Resits at some stage. 

All I want is the Exam Date for 2 students 

I should have mentioned in my original post, our tabular cube does not support DAX Var?