topic Re: Measure returning MAX Values from Child tables in DAX Commands and Tips

Measure returning MAX Values from Child tables

Anonymous — Wed, 19 Jun 2019 20:21:45 GMT

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

Re: Measure returning MAX Values from Child tables

Stachu — Thu, 20 Jun 2019 03:31:44 GMT

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

Re: Measure returning MAX Values from Child tables

Stachu — Thu, 20 Jun 2019 04:22:03 GMT

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

Re: Measure returning MAX Values from Child tables

Anonymous — Thu, 20 Jun 2019 07:16:17 GMT

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?

Re: Measure returning MAX Values from Child tables

Anonymous — Thu, 20 Jun 2019 07:18:19 GMT

Thanks for the reply,

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

Re: Measure returning MAX Values from Child tables

Stachu — Thu, 20 Jun 2019 08:52:24 GMT

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

Re: Measure returning MAX Values from Child tables

Anonymous — Thu, 20 Jun 2019 10:20:05 GMT

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