Forum Discussion

sjrrkb123's avatar
sjrrkb123
Helper III
5 years ago
Solved

Finding Duplicates in a Table

So I have worked with finding duplicates a lot in the past but I am struggling in the current situation. 

What I am trying to do is to find any professional keys that exist in both the "Owner" & "Sponsor Roles." If the professional key exists in both, then the column "Dual Role Check" will equal 1, else 0. This needs to be scoped to a given project key.

Here is what my data looks like:

Project Key Professional Key Role Name
222622 123 Sponsor
222622 105 Director
222622 123 Owner
222622 106 Partner
222622 314 Manager

222623

 455 Sponsor
222623 416 Director
222623 314 Owner
222623 444 Partner
222623 024 Manager


What I want is the following:

Project Key Professional Key Role Name Dual Role Check
222622 123 Sponsor 1
222622 105 Director 0
222622 123 Owner 1
222622 106 Partner 0
222622 314 Manager 0

222623

 455 Sponsor 0
222623 416 Director 0
222623 314 Owner 0
222623 444 Partner 0
222623 024 Manager 0


Here is the function I have tried:

 

 

 

 

 

Dual Role Check = 
var es_key = IF([Role Name] = "Sponsor", [Professional Key], Blank())
var ow_key = IF([Role Name] = "Owner", [Professional Key], Blank())
RETURN

CALCULATE(COUNT('Data'[Professional Key]), 
    FILTER('Data', 'Data'[Project Key] = EARLIER('Data'[Project Key])),
    ow_key = es_key)


Any help would be appreciated!

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    sjrrkb123 , Create a new column like

     

    new column =
    var _1 = countx(filter(Table, [Project Key] = earlier([Project Key]) && [Professional Key] && earlier([Professional Key])),[Project Key])
    return
    if(_1 >1, 1,0)

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    sjrrkb123 , Create a new column like

     

    new column =
    var _1 = countx(filter(Table, [Project Key] = earlier([Project Key]) && [Professional Key] && earlier([Professional Key])),[Project Key])
    return
    if(_1 >1, 1,0)

    • sjrrkb123's avatar
      sjrrkb123
      Helper III
      5 years ago

      amitchandak  it worked Great! I had to tweak it with the following but I appreciate the help greatly.

      new column =
      var _1 = countx(filter(Table, [Project Key] = earlier([Project Key]) && [Professional Key] && earlier([Professional Key]) && RoleName IN {"Sponsor", "Owner"}),[Project Key])
      return
      if(_1 >1, 1,0)

      as I only care about duplicates in those two roles.