Forum Discussion
Removing duplicates with merging logic
Slice = CALCULATETABLE(FILTER('Input Table', 'Input Table'[Product] = "x"))
This is an annonymised version of what it produced:
| Instance | Product | Quantity | Customer |
| 0001 | x | -1 | Bob |
| 0002 | x | -1 | Bob |
| 0003 | x | 4 | Foo |
| 0004 | x | 7 | Bar |
| 0005 | x | 3 | Do |
I need unique rows based on the Customer column. Note 'Bob' is duplicated. I'm not sure how to use DISTINCT in the context of a FILTER. As you see I'm falling early but my full desire is to also handle the merging logic in regard to two aspects.
1. The Instance is a unique value so I'd like this to be handled by concatination, blank or a new string entered 'Multiple'. Whichever is easier.
2. Quantity can also differ between instances. I'd like these to be SUM if they do not equal -1. If any equal -1 then -1 should be entered in the new row.
I've struggled with other answers on this topic. Explinations and pointers to how functions work together would be appreciated. I'm enjoying the early learning phase of DAX.
Addcolumns(
Distinct( tbl[customer]),
"Instances",
calculate( concatenatex( values(tbl[instance]), tbl[instance], ", ")),
"Quantity",
Var sumQuanity = CALCULATE( sum(tbl[quantity] ))
Var minQuantity = calculate( min( tbl[quantity] ))
Return
If( minQuanity = -1, minQuanity, sumQuantity)
)
6 Replies
Addcolumns(
Distinct( tbl[customer]),
"Instances",
calculate( concatenatex( values(tbl[instance]), tbl[instance], ", ")),
"Quantity",
Var sumQuanity = CALCULATE( sum(tbl[quantity] ))
Var minQuantity = calculate( min( tbl[quantity] ))
Return
If( minQuanity = -1, minQuanity, sumQuantity)
)
This worked well. Thanks so much.
The variables just had some typos. Otherwise flawless.
I noticed that. the sum and min operate over the whole column not grouped. So for my data min is always -1
The sum and mine are calculated within addcolumns. Addcolumns is a iterator, and we are iterating over each customer. The sum and min are wrapped in calculate so context transition applies the current row context of customer into the calculation
Hi, hewgreen
From screenshot you provided it don't seems like duplicate, because it has two instance (0001,0002) and customer(Bob) has value for both instance.
Please provide some sample row data to understand better your problemBest Regards,
DangarIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I've used the term duplicate incorrectly. Within the scope of Customer 'Bob' is duplicated. Therefore I'd like the merge these rows.
