Removing duplicates with merging logic

[hewgreen]

I'm struggling to combine functions correctly (such as FILTER and DISTINCT) in a CALCULATETABLE query. I used the following to generate a slice of a larger input table based on the Product string:

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.

[Dangar332]

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 problem

Best Regards,
Dangar

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hewgreen]

I've used the term duplicate incorrectly. Within the scope of Customer 'Bob' is duplicated. Therefore I'd like the merge these rows.

[Deku · Accepted solution]

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)

)

[hewgreen]

I noticed that. the sum and min operate over the whole column not grouped. So for my data min is always -1

[Deku]

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

[hewgreen]

This worked well. Thanks so much.

The variables just had some typos. Otherwise flawless.