locked
DAX Measure context RRS feed

  • Question

  • 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

    Wednesday, June 19, 2019 3:27 PM

Answers

  • hi,

    StudentHistory[ClassType] = "Audit"

                        &&[ExamStatus] = "Resit"

    this code is translated internally in DAX as following:

    Filter(All(StudentHistory[ClassType], StudentHistory[ExamStatus]), 

    StudentHistory[ClassType] = "Audit"

                        &&[ExamStatus] = "Resit")

    (All) is the filter remover. It will assign the CALCULATE result to all [ExamDate] in the initial filter context.

    You may try KEEPFILTERS to retain the result of CALCULATE in the initial filter context.

    KEEPFILTERS(

    Filter(All(StudentHistory[ClassType], StudentHistory[ExamStatus]), 

    StudentHistory[ClassType] = "Audit"

                        &&[ExamStatus] = "Resit")

    )



    Saturday, June 22, 2019 9:17 AM