DAX Count Records that have changed values

[paulvans182]

Good afternoon,

I wonder if someone can help me with a DAX expression

I have a table with the list of scores for two tests for students in the format shown below:

Student	Test	Score
Mark	Test 1	50
Mark	Test 2	55
Tom	Test 1	45
Tom	Test 2	40
Travis	Test 1	48
Travis	Test 2	72

I am looking for a method to COUNT the number of students whose scores increased from Test 1 to Test 2.

For example, in the example above, Mark and Travis improved their results between the two tests so the answer should be 2.

I am not sure what DAX expression I should use.

Please could someone help me?

Thanks very much

Paul

[DataInsights · Accepted solution]

@paulvans182,

Try the following:

1. Create a relationship as shown below. The Student table contains a list of all student names.

2. Create this measure:

Count Increased Test Score = 
SUMX (
    Student,
    VAR vStudent = Student[Student Name]
    VAR vTest1 =
        FILTER (
            ALL ( TestScore ),
            TestScore[Student] = vStudent
                && TestScore[Test] = "Test 1"
        )
    VAR vTest2 =
        FILTER (
            ALL ( TestScore ),
            TestScore[Student] = vStudent
                && TestScore[Test] = "Test 2"
        )
    VAR vTest1Score =
        MAXX ( vTest1, TestScore[Score] )
    VAR vTest2Score =
        MAXX ( vTest2, TestScore[Score] )
    RETURN
        IF ( vTest1Score < vTest2Score, 1, 0 )
)

[paulvans182]

Thank you, this works perfectly.   I really appreciate your help

Kind regards,

Paul

[DataInsights]

@paulvans182, glad to hear that.

Please consider marking this as the solution to help other members find it more quickly.

[paulvans182]

Hi @DataInsights ,

Sorry I thought I had.  Believe I have done so now.  Thanks again.