cancel
Showing results for
Did you mean:
Regular Visitor

## Measure returning MAX Values from Child tables

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 (

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

Roy

6 REPLIES 6
Super User II

## Re: Measure returning MAX Values from Child tables

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

Thank you for the kudos 🙂

Proud to be a Datanaut!

Super User II

## Re: Measure returning MAX Values from Child tables

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"
)
Thank you for the kudos 🙂

Proud to be a Datanaut!

Regular Visitor

## Re: Measure returning MAX Values from Child tables

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?

Regular Visitor

## Re: Measure returning MAX Values from Child tables

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

Super User II

## Re: Measure returning MAX Values from Child tables

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"
)
Thank you for the kudos 🙂

Proud to be a Datanaut!

Highlighted
Regular Visitor

## Re: Measure returning MAX Values from Child tables

Stachu

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 (

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

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!