Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |