Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
NickProp28
Post Partisan
Post Partisan

Create measure and depend on whether it is duplicate

Dear Community,

 

I have data as below 

JobNTransactionNNew Column**
J001T001J001
J002T002Duplicate TransactionN
J003T002Duplicate TransactionN
J004T003Duplicate TransactionN
J005T003Duplicate TransactionN
J006T004J006
J007T005J007

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.

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @NickProp28 ,

 

Here are the steps you can follow:

1. Create calculated colunm.

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

2. Result.

v-yangliu-msft_0-1605232029522.png

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.

View solution in original post

10 REPLIES 10
v-yangliu-msft
Community Support
Community Support

Hi  @NickProp28 ,

 

Here are the steps you can follow:

1. Create calculated colunm.

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

2. Result.

v-yangliu-msft_0-1605232029522.png

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 a lot. 

v-yangliu-msft
Community Support
Community Support

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.

v-yangliu-msft_0-1605172379482.jpeg

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)

NickProp28_0-1605173881931.png

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

NickProp28
Post Partisan
Post Partisan

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
Super User
Super User

@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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Dear @amitchandak ,

 

My data as below,

NickProp28_0-1605080482210.png

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
Post Partisan
Post Partisan

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
Super User
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]

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Dear @amitchandak ,

 

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.