Count and write duplicates from a concatenate into a new column

[LucienF38]

Hello team PowerBI,

I have a concatenate column that I have created with the formula "COMBINEVALUES" (see below the formula and the result):

FormulaResult

I would like to write in a new column "OK" if it's the first iteration (the earliest in date) and "duplicate" for the duplicates (same concatenate but later in date), as you can see below (sorry for the french ahah) :

So the question is : Which formula should I use in DAX? 

I'd like to precise that all of this data is sorted by date. 

Thank you very much for your help in advance !

Lucien 🙂 

[Cmcmahan · Accepted solution]

Sure. Here's a measure that will do that if you have one row selected:

Ok/Double = IF( COUNTROWS(FILTER(ALL(Table), Table[Date] < SELECTEDVALUE(Table[Date]) && Table[Concatenate] = SELECTEDVALUE(Table[Concatenate]) ) > 0, "doublon", "ok") 

If you want a calculated column instead, replace SELECTEDVALUE with EARLIER.

[Anonymous]

Hi @LucienF38 ,

Please try using these steps:

1- Edit queries and add the index column first.

2-change the datatype of index column to whole #

3- apply and save

4- add the column (screenshot below)

5 - and include this formula

Column = 
VAR dups= 
CALCULATE(count('Sheet10'[Concatenate]),filter(all('Sheet10'),
[Index]<=EARLIER([Index])
  && [Concatenate]=EARLIER((Sheet10[Concatenate])
  )
)
)
return
IF(dups>=2,"DUPS","OK")

6- when you add this in Table Visualization, make sure, Index column is DO not summarize, ese it will give incorrect output.

my output:

Thanks,

Tejaswi

[LucienF38]

Thanks for replying tejaswidmello,

The thing is that my data is coming from different tables. I can't do it in Edit Query mode...

Do you have a solution in DAX?