cancel
Showing results for
Did you mean:
Highlighted Helper III

## 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:

 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.

Thanks very much

Paul

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Solution Sage

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 4
Highlighted Solution Sage

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 )
)``````
Highlighted Helper III

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

Kind regards,

Paul

Highlighted Solution Sage

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

Highlighted Helper III

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