Forum Discussion

Benjamin_Eureka's avatar
Benjamin_Eureka
Frequent Visitor
6 years ago
Solved

Database design for comparing rows

Hello friends,

I face a challenge and I don't know how to address it. I have a big flat table with the test results. It contains data on all completed exams (different types) and candidates who took them. One of the things I have to do, for example, is to check if the candidates who took "Exam A" got better results on "Exam B."

I have no idea where to start. I have made several boards and did several edx courses. I even read a book 😉 but I have no idea how to appease this. I was hoping someone could point me in the right direction. Which book should I read/what video to watch, etc. I'm willing to work for it 😉

Thnx!

Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    6 years ago

    Hi Benjamin_Eureka,

     

    Is the output in your data your expected output? But as parry2k said, the data you provided lack candidate column. Would you please check for it?

     

    If you want to filter out candidate that pass both Exam A and Exam B, please refer to the measure below:

     

     

    Measure =

VAR A =

    CALCULATE (

        MAX ( 'Table'[result] ),

        ALLEXCEPT ( 'Table', 'Table'[candidate] ),

        'Table'[exam] = "ExamA"

    )

VAR B =

    CALCULATE (

        MAX ( 'Table'[result] ),

        ALLEXCEPT ( 'Table', 'Table'[candidate] ),

        'Table'[exam] = "ExamB"

    )

RETURN

    IF ( A = "Yes" && B = "Yes", 1, 0 )

     

     

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

13 Replies