Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |