Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to 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.
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.
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.
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!
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.
@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
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.
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'
@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]
Dear @amitchandak ,
Is it my reply and pbix has clear your doubt?
If yes please kindly advice me on it. Appciate your time.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
89 | |
87 | |
81 | |
64 | |
49 |
User | Count |
---|---|
123 | |
109 | |
88 | |
68 | |
67 |