Unique or Duplicate

[FelippeAzevedo7]

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 · Accepted solution]

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~

[FelippeAzevedo7]

Hello

Many tks for the support.