Forum Discussion
Post PartisanCreate measure and depend on whether it is duplicate
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.
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.
10 Replies
- amitchandak
Super User
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]
NickProp28Dear amitchandak ,
Is it my reply and pbix has clear your doubt?
If yes please kindly advice me on it. Appciate your time.
- 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 , 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 ,
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.
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 Anonymous ,
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!
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 Anonymous ,
Thanks a lot.
