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
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
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.
You need to add a comparison of two Exams' time to the if criteria, which is similar to my measure above
Ah of course. Using variables is still new for me; sounds like a learning opportunity. I have enough to play around. Thanks a lot!