Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
paulvans182
Helper III
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:

StudentTestScore
MarkTest 150
MarkTest 255
TomTest 145
TomTest 240
TravisTest 148
TravisTest 272

 

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

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

@paulvans182,

 

Try the following:

 

1. Create a relationship as shown below. The Student table contains a list of all student names.

 

DataInsights_0-1602105753003.png

 

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 )
)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
DataInsights
Super User
Super User

@paulvans182,

 

Try the following:

 

1. Create a relationship as shown below. The Student table contains a list of all student names.

 

DataInsights_0-1602105753003.png

 

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 )
)




Did I answer your question? Mark my post as a solution!

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @DataInsights ,

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.