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.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors