Database design for comparing rows

[Benjamin_Eureka]

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!

[parry2k]

@Benjamin_Eureka hey could you please post the sample data and expected output. Make sure post it is in a table format, or share excel file, you can share excel using one drive/google drive.

[Benjamin_Eureka]

@parry2k 

Thank you for your quick reply. Here is a link with some sample data. The exam types are in Dutch, but you'll get the point.

https://eurekagroep-my.sharepoint.com/:x:/g/personal/benjamin_eureka-groep_nl/EQ6bgdyF6elNrkopSZ5SAO...

[parry2k]

@Benjamin_Eureka Thanks but no idea how you get to those numbers? Could you explain the logic?

[Benjamin_Eureka]

ah, I'm so sorry. I filled the output table with dummy numbers. The idea is that I can see, of the people who completed 'Exam B', if they also finished 'Exam A' (dummy names). 

For instance:

There are 30 people who completed 'Exam B'. 20 of them also completed 'Exam A'. I want to check if those 20 received higher grades than the other 10. 

Does this makes sense? I'm sorry for the lack of clarity.

[parry2k]

@Benjamin_Eureka I wish you had made excel file much easy to digest, we are talking Exam a and b, and in excel sheet you have all various text (i cannot relate) and the numbers in excel don't match with anything. would you mind putting the file again and explain, help me to get the help.

[Benjamin_Eureka]

I'm sorry. Another try. I have updated the file to match the example I gave. Now you see a simple output table with dummy numbers as well as an example of the input table. The question I have is:

How can I check if candidates who took Exam A had a higher grade for Exam B than the people who did not take Exam A before?

Thanks for your efforts

The file: https://eurekagroep-my.sharepoint.com/:x:/g/personal/benjamin_eureka-groep_nl/EQ6bgdyF6elNrkopSZ5SAO...

[parry2k]

@Benjamin_Eureka Maybe I'm not reading it correctly, there is no candidate in your sample data who took exam A and also exam b so how we can answer the 1st question?

[Benjamin_Eureka]

Like I said: it's dummy data. I cannot share the real data due to company limitations. But that's besides the point. The data itself doesn't matter. The question is how I can find out if a candidate who passed Exam B already passed Exam A. Or in a more abstract way: how can I find out if a certain entry is available in slightly different form anywehere else in the database. I am looking for the abstraction of the solution; the file provided is purely for clarification purposes.

[v-deddai1-msft · Accepted solution]

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

[Benjamin_Eureka]

@v-deddai1-msft Thnx for your reply. The candidate column is not lacking, it is represented by the first column: Candidate Id. This number is the same as a unique candidate name; every candidate has it's own number. 

Thank you for the measure! One thing though, is that it doesn't make the distinction between candidates who first took Exam A and then took Exam B vs. the candidates who took Exam B first before Exam A. But it does definitely point me in the right direction. Thanks for your effort.

[v-deddai1-msft]

You need to add a comparison of two Exams' time to the if criteria, which is similar to my measure above

[Benjamin_Eureka]

Ah of course. Using variables is still new for me; sounds like a learning opportunity. I have enough to play around. Thanks a lot!

[parry2k]

@Benjamin_Eureka understood it is fake data but it has to make sense so that I can test the solution at my end before giving you something which doesn't make sense. I am a bit surprised to see the sample data which is not at all useable to test the solution. I hope someone else can help here. I don't like to provide a solution that I cannot even test and see working. I surely know what I need to do but I need to test it. Good luck!