Seperate duplicate and unique

[tejapowerbi123]

Hello Community,
I am trying to get the unique id and duplicate it from the below dataset. Can anyone have some suggestions? 
I have tried with column from example in Pow

[Anonymous · Accepted solution]

Hi  @tejapowerbi123 ,

Here are the steps you can follow：

1. Create calculated column.

ID1 = MID('Dataset'[ID],4,6)

ID2 =
 MID('Dataset'[ID],22,6)

IF1 =
var _select=SELECTCOLUMNS('Dataset',"D1",[ID1])
return
IF(
    'Dataset'[ID2] in _select,1,0)

IF2 =
var _select=SELECTCOLUMNS('Dataset',"D2",[ID2])
return
IF(
    'Dataset'[ID1] in _select,1,0)

Unique =
SWITCH(
    TRUE(),
    'Dataset'[IF1]=0&&'Dataset'[IF2]=1,'Dataset'[ID2],
    'Dataset'[IF1]=1&&'Dataset'[IF2]=0,'Dataset'[ID1],
    'Dataset'[IF1]=0&&'Dataset'[IF2]=0,'Dataset'[ID1]
)

Duplicate =
SWITCH(
    TRUE(),
    'Dataset'[IF1]=0&&'Dataset'[IF2]=1,'Dataset'[ID1],
    'Dataset'[IF1]=1&&'Dataset'[IF2]=0,'Dataset'[ID2],
    'Dataset'[IF1]=0&&'Dataset'[IF2]=0,'Dataset'[ID2]   
)

2. Create calculated table.

Final Result =
SUMMARIZE('Dataset','Dataset'[ID],'Dataset'[Unique],'Dataset'[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

[tejapowerbi123]

@Anonymous Thank you so much 🙂😀

[lbendlin]

Do you need this in DAX or can it be a Power Query solution?  How big is your actual table?

[tejapowerbi123]

@lbendlin 
Anything works for me dax or power query.My original source is in Azure SQL and size of the table is approximate 12,000 Rows and 6 columns.

[lbendlin]

provide some more sample data, in usable format please. Not as screenshot.

[tejapowerbi123]

@lbendlin 
Here is the dataset and result for your reference,

Dataset

ID

id:383249,related id:386244

id:387739,related id:383249

id:387692,related id:383249

id:392116,related id:386244

id:387739,related id:392116

id:395555,related id:395556

Final Result
ID	Unique	Duplicate
id:383249,related id:386244	386244	383249
id:387739,related id:383249	387739	383249
id:387692,related id:383249	387692	383249
id:392116,related id:386244	386244	392116
id:387739,related id:392116	387739	392116
id:395555,related id:395556	395555	395556

[lbendlin]

Please explain the expected result in line 1 and 4. 386244 is not unique.

[tejapowerbi123]

@lbendlin sorry it was a typo,

Dataset

ID

id:383249,related id:386244

id:387739,related id:383249

id:387692,related id:383249

id:392116,related id:386245

id:387739,related id:392116

id:395555,related id:395556

Final Result
ID	Unique	Duplicate
id:383249,related id:386244	386244	383249
id:387739,related id:383249	387739	383249
id:387692,related id:383249	387692	383249
id:392116,related id:386244	386245	392116
id:387739,related id:392116	387739	392116
id:395555,related id:395556	395555	395556

[lbendlin]

Please explain line 6. Why is 395556 a duplicate?

As you can see there are many scenarios to consider. Please rethink your requirement.

[tejapowerbi123]

@lbendlin If it is just one the row then keep it as it is that is the explaniation for line 6.