Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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.
@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 |
Please explain the expected result in line 1 and 4. 386244 is not unique.
@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
|
Please explain line 6. Why is 395556 a duplicate?
As you can see there are many scenarios to consider. Please rethink your requirement.
@lbendlin If it is just one the row then keep it as it is that is the explaniation for line 6.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |