Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Detecting duplicates for data validation across multiple tables for non-zero values

Pulling out my hair over this one. 

I need to determine if non-zero duplicates are present within a data submission. Specifically, I tried to solve this by checking whether or not for non-zero values, the count of values was equal to the distinct count of data ID values for a submission.

 

ProtocolCheck =
Calculate(
    if(
        COUNT(SubmissionBreakdown[DataId]) = DISTINCTCOUNT(SubmissionBreakdown[DataID]),1,0),
        filter(SubmissionBreakdown, SubmissionBreakdown[SubmissionId] = Submission[SubmissionId]), SubmissionImage[ImageID] <>0)
 
If I run this chunk of code, it doesn't error out, but runs for 20+ minutes. Open to any suggestions on bringing this to life.
 

Sample Data and expected results:

 

SubmissionBreakdown table

SubmissionIdDataId
11
12
13
14
20
2

2

23
24
30
32
32
34

 

SubmissionId table

SubmissionIdProtocolCheck
11
21
30
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    The solution didn't work but solved it in a different way - simply counted the number of data points of a particular value and then flagged if it was over an acceptable value i.e. if there was more than 1 of a value, it fails protocol through an if statement.

    Sample here checks whether or not there are too many ones or twos. To keep going for 3 and 4, just nest the OR statements.

    If(OR(If(Countrows(CALCULATETABLE(SubmissionBreakdown, Filter(SubmissionBreakdown, SubmissionBreakdown[DataId] = 1), Filter(SubmissionBreakdown, SubmissionBreakdown[SubmissionId] = Submission[submissionid]))) > 1, 1, 0), If(Countrows(CALCULATETABLE(SubmissionBreakdown, Filter(SubmissionBreakdown, SubmissionBreakdown[DataId] = 2), Filter(SubmissionBreakdown, SubmissionBreakdown[SubmissionId] = Submission[submissionid]))) > 1, 1, 0)) = TRUE, 1, 0)



2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    Anonymous Maybe:

    Protocol Check = 
  VAR __Count1 = COUNTROWS('Table')
  VAR __Count2 = COUNTROWS(SUMMARIZE('Table',[SubmissionID],[DataId]))
RETURN
  IF(__Count1 = __Count2,1,0)
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    The solution didn't work but solved it in a different way - simply counted the number of data points of a particular value and then flagged if it was over an acceptable value i.e. if there was more than 1 of a value, it fails protocol through an if statement.

    Sample here checks whether or not there are too many ones or twos. To keep going for 3 and 4, just nest the OR statements.

    If(OR(If(Countrows(CALCULATETABLE(SubmissionBreakdown, Filter(SubmissionBreakdown, SubmissionBreakdown[DataId] = 1), Filter(SubmissionBreakdown, SubmissionBreakdown[SubmissionId] = Submission[submissionid]))) > 1, 1, 0), If(Countrows(CALCULATETABLE(SubmissionBreakdown, Filter(SubmissionBreakdown, SubmissionBreakdown[DataId] = 2), Filter(SubmissionBreakdown, SubmissionBreakdown[SubmissionId] = Submission[submissionid]))) > 1, 1, 0)) = TRUE, 1, 0)