Create measure and depend on whether it is duplicate

[NickProp28]

Dear Community,

I have data as below 

JobN	TransactionN	New Column**
J001	T001	J001
J002	T002	Duplicate TransactionN
J003	T002	Duplicate TransactionN
J004	T003	Duplicate TransactionN
J005	T003	Duplicate TransactionN
J006	T004	J006
J007	T005	J007

Supposedly TransactionN is unique, which mean JobN only have one TransactionN.

But tats weird in my data, 2 different JobN is having same common TransactionN.

So I would like to add one column (refer back to JobN if not duplicate) .But how do I make it? Since create one new column will checking line by line and doesnt know whether the transactionN will appear somewhere and isit duplicate

Appreciate any helps.

[v-yangliu-msft · Accepted solution]

Hi  @NickProp28 ,

Here are the steps you can follow：

1. Create calculated colunm.

New Colunm = IF([JNC]=[TNC],[JobNumber],"Duplicate")

2. Result.

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NickProp28]

Dear @v-yangliu-msft ,

Thanks a lot. 

[v-yangliu-msft]

Hi  @NickProp28 ,

Here are the steps you can follow：

1. Create calculated table.

TNC = CALCULATE(COUNT('Table'[TransactionNumber]),ALLEXCEPT('Table','Table'[TransactionNumber]))

JNC = CALCULATE(COUNT('Table'[JobNumber]),ALLEXCEPT('Table','Table'[JobNumber]))

2. Create measure.

New = IF(MAX('Table'[JNC])=MAX('Table'[TNC]),MAX('Table'[JobNumber]),"Duplicate")

3. Result.

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NickProp28]

Dear @v-yangliu-msft ,

Thanks for your response!
Its work well when create the DAX and show 'Duplicate'. But I would like to create a new column and indicate all my raw data whenever is it duplicate. Is it possible to do that ? I try the code you provided, but its show all the MAX JobNumber. (Create new column will more easy for me to have a report filter)

Here the pbix: https://ufile.io/hzdz4flh
Thanks again for your help!

[NickProp28]

Dear @amitchandak ,

Kindly take a look on this PBIX example(https://ufile.io/twxcj922)

Due to this issue, I have double count the transactionnumber even I have put distinctcout on it. 

Transaction number is unique for most of time. But there will somehow show in some special case.

[amitchandak]

@NickProp28 , there are no dates in your example. Can you share a better sample with the output? I think the date need to considered to call it duplicate

[NickProp28]

Dear @amitchandak ,

My data as below,

Hope can create a column to indicate it is duplicate.

Suspossedly, One TransactionNumber is only have one JobNumber.

But as you can see, different jobnumber is sharing one TransactionNumber.

[NickProp28]

Dear @amitchandak ,

Thanks for your response.

Measure you given will show all result in duplicate. Because I have different day and time for one JobN. And I will have different row. 

My question is, JobN is only have one TransactionN.

But if different JobN sharing the same TransactionN. New measure will result it as 'Duplicate'

[amitchandak]

@NickProp28 , Not very clear, what column you need. You can have column like this to check duplicate

If(countx(filter(Table,[TransactionN] = earlier(TransactionN)),[TransactionN])+0 >0, "Duplicate",[JobN])

Inside job duplicate

If(countx(filter(Table,[TransactionN] = earlier(TransactionN) && [JobN] = earlier(JobN)),[TransactionN])+0 >0, "Duplicate",[JobN])

You can have to combine unique column

Key = [JobN] & "-" & [TransactionN]

[NickProp28]

Dear @amitchandak ,

Is it my reply and pbix has clear your doubt?
If yes please kindly advice me on it. Appciate your time.