topic Unique or Duplicate in DAX Commands and Tips

Unique or Duplicate

FelippeAzevedo7 — Fri, 17 May 2024 14:06:01 GMT

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

Re: Unique or Duplicate

xifeng_L — Fri, 17 May 2024 15:11:59 GMT

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~

Re: Unique or Duplicate

FelippeAzevedo7 — Fri, 17 May 2024 19:09:45 GMT

Hello

Many tks for the support.