Forum Discussion
Database design for comparing rows
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
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.
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!