Forum Discussion
Seperate duplicate and unique
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
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
10 Replies
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
Anonymous Thank you so much 🙂😀
Do you need this in DAX or can it be a Power Query solution? How big is your actual table?
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.provide some more sample data, in usable format please. Not as screenshot.
