Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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 multip...
DAX
Stachu's avatar
Stachu
Icon for Community Champion rankCommunity Champion
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

Thanks for the reply,

 

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

  • Stachu's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

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