cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ryand09 Regular Visitor
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 (

                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

6 REPLIES 6
Stachu Super Contributor
Super Contributor

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Stachu Super Contributor
Super Contributor

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"
    )
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Highlighted
ryand09 Regular Visitor
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?

 

ryand09 Regular Visitor
Regular Visitor

Re: Measure returning MAX Values from Child tables

Thanks for the reply,

 

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

Stachu Super Contributor
Super Contributor

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"
)
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

ryand09 Regular Visitor
Regular Visitor

Re: Measure returning MAX Values from Child tables

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 405 members 4,018 guests
Please welcome our newest community members: