topic Re: Removing duplicates with merging logic in DAX Commands and Tips

Removing duplicates with merging logic

hewgreen — Wed, 05 Mar 2025 07:18:15 GMT

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.

Re: Removing duplicates with merging logic

Deku — Wed, 05 Mar 2025 07:30:12 GMT

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)

)

Re: Removing duplicates with merging logic

Dangar332 — Wed, 05 Mar 2025 07:58:40 GMT

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.

Re: Removing duplicates with merging logic

hewgreen — Wed, 05 Mar 2025 10:11:48 GMT

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

Re: Removing duplicates with merging logic

hewgreen — Wed, 05 Mar 2025 10:27:47 GMT

This worked well. Thanks so much.

The variables just had some typos. Otherwise flawless.

Re: Removing duplicates with merging logic

hewgreen — Wed, 05 Mar 2025 11:14:38 GMT

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

Re: Removing duplicates with merging logic

Deku — Wed, 05 Mar 2025 11:36:39 GMT

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