cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Post Partisan

## Create 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.

1 ACCEPTED SOLUTION
Community Support

Hi  @NickProp28 ,

Here are the steps you can follow：

1. Create calculated colunm.

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

2. Result.

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 10
Community Support

Hi  @NickProp28 ,

Here are the steps you can follow：

1. Create calculated colunm.

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

2. Result.

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.

Post Partisan

Dear @v-yangliu-msft ,

Thanks a lot.

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.

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.

Post Partisan

Dear @v-yangliu-msft ,

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

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.

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

Post Partisan

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.

Post Partisan

Dear @amitchandak ,

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'

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]

Post Partisan

Dear @amitchandak ,

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors