Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Flag for duplicate data

Hi All,
I want to create the custom column "Flag" based on the EMPNO and ENAME data.
When EMPNO =1001, ENAME values are E1 and E2, Flag should be "Duplicate"
When EMPNO =1003 selection, ENAME values are E1 and E2, Flag should be "Duplicate"

When EMPNO =1002, ENAME values are E2, Flag should be "Unique"
When EMPNO =1005, ENAME values are E5, Flag should be "Unique"

Logic: If more than one ENAME value exists for one EMPNO selection, Flag should be "Duplicate"
If one ENAME value exists for one EMPNO selection, Flag should be "Duplicate"


Please help me to achieve. Thanks in advance.

 

Power BI file : https://drive.google.com/file/d/1B2hc_ZA2hoIAChB9InoP692FQuPH3rCv/view?usp=sharinghttps://drive.google.com/file/d/1B2hc_ZA2hoIAChB9InoP692FQuPH3rCv/view?usp=sharing 

Interesting Links
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous ,  a New column

    new =

    var _cnt = countx(filter(Table, [EMPNO] = earlier([EMPNO]) ), [EMPNO])

    return

    if(_cnt>1, "Duplicate", "Unique")

     

     

    or

     

     

    new =

    var _cnt = countx(filter(Table, [EMPNO] = earlier([EMPNO]) &&  [ENAME] = earlier([ENAME]) ), [EMPNO])

    return

    if(_cnt>1, "Duplicate", "Unique")

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous ,  a New column

    new =

    var _cnt = countx(filter(Table, [EMPNO] = earlier([EMPNO]) ), [EMPNO])

    return

    if(_cnt>1, "Duplicate", "Unique")

     

     

    or

     

     

    new =

    var _cnt = countx(filter(Table, [EMPNO] = earlier([EMPNO]) &&  [ENAME] = earlier([ENAME]) ), [EMPNO])

    return

    if(_cnt>1, "Duplicate", "Unique")