Forum Discussion

paulvans182's avatar
paulvans182
Helper III
5 years ago
Solved

DAX Count Records that have changed values

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:

StudentTestScore
MarkTest 150
MarkTest 255
TomTest 145
TomTest 240
TravisTest 148
TravisTest 272

 

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

Need Help
  • DataInsights's avatar
    DataInsights
    5 years ago

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

4 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    5 years ago

    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's avatar
      paulvans182
      Helper III
      5 years ago

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

       

      Kind regards,

      Paul

       

      • DataInsights's avatar
        DataInsights
        Super User
        5 years ago

        paulvans182, glad to hear that.

         

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