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
Solved! Go to Solution.
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 )
)
Proud to be a Super User!
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 )
)
Proud to be a Super User!
Thank you, this works perfectly. I really appreciate your help
Kind regards,
Paul
@paulvans182, glad to hear that.
Please consider marking this as the solution to help other members find it more quickly.
Proud to be a Super User!
Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.