Forum Discussion

FelippeAzevedo7's avatar
FelippeAzevedo7
Helper I
2 years ago
Solved

Unique or Duplicate

Hello All

 

I have a data file where the ticket number sometimes appears more than once and I'd like to create a column that says single or duplicate, but that when there is a duplicate the first incident comes up as single and the others as duplicate.
I've used a formula, but this way they both come up as duplicates and not just the second one.

 

Unique/Duplicate =
VAR currRowValue = SELECTEDVALUE('YourTable'[YourColumn])
VAR countPrevOccur =
CALCULATE(
COUNTROWS(
FILTER(
'YourTable',
'YourTable'[YourColumn] = currRowValue
)
)
)
RETURN
IF(countPrevOccur > 1, "Duplicate", "Unique")

 

I need another opinion to solve this problem. I am not expert with power bi yet. I know how to do it with excel but not using power bi. Tks

  • xifeng_L's avatar
    xifeng_L
    2 years ago

    Hi FelippeAzevedo7 ,

     

    If you want the first item of a duplicate ticket to appear as a single one, you will also need a sort field to determine the order, such as date, etc.

     

    You can refer to the column expression below.

     

     

    Unique/Duplicate = 
VAR CurIndex = 'Table'[Index]
VAR MinIndex = CALCULATE(MIN('Table'[Index]),ALLEXCEPT('Table','Table'[Ticket]))
RETURN
IF(CurIndex=MinIndex,"Single","Duplicate")

     

     

    Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

     

    Thank you~

     

     

     

2 Replies

  • xifeng_L's avatar
    xifeng_L
    Super User
    2 years ago

    Hi FelippeAzevedo7 ,

     

    If you want the first item of a duplicate ticket to appear as a single one, you will also need a sort field to determine the order, such as date, etc.

     

    You can refer to the column expression below.

     

     

    Unique/Duplicate = 
VAR CurIndex = 'Table'[Index]
VAR MinIndex = CALCULATE(MIN('Table'[Index]),ALLEXCEPT('Table','Table'[Ticket]))
RETURN
IF(CurIndex=MinIndex,"Single","Duplicate")

     

     

    Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

     

    Thank you~