Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Checking for different values in duplicates

Good day all,   I have a table in Power Bi that looks similar to this: What I need to get is a column called Age_Check that would output 1 if for every group of records with the same name wh...
Tips and Tricks
Tutorial Requests
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , Seem like missed age logic, Try this

     

    Age_check =
    var _1 = countx(filter(Table,table[Name] = earlier(Table[Name])),Table[Name])
    var _2 = countx(filter(Table,table[Name] = earlier(Table[Name]) && table[Age] = earlier(Table[age])),Table[Name])
    return
    if(_1 >1 && _1<> _2 ,1,0)

Anonymous's avatar
Anonymous
Not applicable
5 years ago

amitchandak Please could you input the column names I used in my Example data.

amitchandak's avatar
amitchandak
Super User
5 years ago

Anonymous , You have used a name, I used the same

Age_check = if(countx(filter(Table,table[Name] = earlier(Table[Name])),Table[Name])>1,1,0)

 

or we can use ID

 

Age_check = if(countx(filter(Table,table[ID] = earlier(Table[ID])),Table[ID])>1,1,0)

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    amitchandak Thank you for the solution, but it does not address the Age factor. I have tested it and what your solution does is to mark all records with duplicate Name as 1. However I need it to check if Age is the same for all cases where Name is duplicated. As I explained, if Name is duplicated it should check all instaces of age and if there is a difference in the age then it should record 1 for all the affected records. 

    • amitchandak's avatar
      amitchandak
      Super User
      5 years ago

      Anonymous , Seem like missed age logic, Try this

       

      Age_check =
      var _1 = countx(filter(Table,table[Name] = earlier(Table[Name])),Table[Name])
      var _2 = countx(filter(Table,table[Name] = earlier(Table[Name]) && table[Age] = earlier(Table[age])),Table[Name])
      return
      if(_1 >1 && _1<> _2 ,1,0)

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        This worked! Appreciate the support