Concate Customer Wise

[Anonymous]

Dear All,

I am using this below Dax query to concate the items for each customer bought in the same month.

Combine = CONCATENATEX (SUMMARIZE (

FILTER ( 'ID-Item','ID-Item'[BillId]=EARLIER('ID-Item'[BillId])),

'ID-Item'[Item ],'ID-Item'[CustomerId]

),'ID-Item'[Item ],",")

I am getting results as below which is bill wise concate that is wrong:
Item BillIdCustomerIdMonthCombine

chicken	1	abc	Jan	chicken
chicken	3	xyz	Jan	chicken,Eggs
chicken	6	pol	Jan	chicken,Mutton,Eggs
chicken	7	sat	Jan	chicken
chicken	10	abc	Feb	chicken
Mutton	2	abc	Jan	Mutton
Mutton	4	sab	Jan	Mutton
Mutton	6	pol	Jan	chicken,Mutton,Eggs
Mutton	8	sat	Jan	Mutton
Eggs	3	xyz	Jan	chicken,Eggs
Eggs	5	frq	Jan	Eggs
Eggs	6	pol	Jan	chicken,Mutton,Eggs
Eggs	9	bat	Jan	Eggs
Eggs	11	abc	Feb	Eggs

I need customer wise Concate not bill wise as below result:

Item	BillId	CustomerId	Month	Combine
chicken	1	abc	Jan	chicken,Mutton
chicken	3	xyz	Jan	chicken,Eggs
chicken	6	pol	Jan	chicken,Mutton,Eggs
chicken	7	sat	Jan	chicken,Mutton
chicken	10	abc	Feb	chicken,Eggs
Mutton	2	abc	Jan	chicken,Mutton
Mutton	4	sab	Jan	Mutton
Mutton	6	pol	Jan	chicken,Mutton,Eggs
Mutton	8	sat	Jan	chicken,Mutton
Eggs	3	xyz	Jan	chicken,Eggs
Eggs	5	frq	Jan	Eggs
Eggs	6	pol	Jan	chicken,Mutton,Eggs
Eggs	9	bat	Jan	Eggs
Eggs	11	abc	Feb	chicken,Eggs

Please help me in correction of this Dax

thanks

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

You can update your calculated column [Combine] as below to get customer wise combination:

Combine =

CONCATENATEX (

FILTER (

'ID-Item',

'ID-Item'[CustomerId] = EARLIER ( 'ID-Item'[CustomerId] )

&& 'ID-Item'[Month] = EARLIER ( 'ID-Item'[Month] )

),

'ID-Item'[Item],

","

)

Best Regards

[amitchandak]

@Anonymous , Try a measure like

Combine = CONCATENATEX (SUMMARIZE (
FILTER ( allselected('ID-Item'),'ID-Item'[BillId]=Max('ID-Item'[BillId])),
'ID-Item'[Item ],'ID-Item'[CustomerId]
),'ID-Item'[Item ],",")

[Anonymous]

Dear @amitchandak 
I am getting this error while using your DAX query.
Please correct it.
Thanks

[amitchandak]

@Anonymous , Oh, you are trying a column. Try like

Combine = CONCATENATEX (
FILTER ( allselected('ID-Item'),'ID-Item'[BillId]=earlier('ID-Item'[BillId])),'ID-Item'[Item ],",")

[Anonymous]

@amitchandak 
i am getting below result :

Item BillIdCustomerIdMonthCombine

chicken	1	abc	Jan	chicken
chicken	3	xyz	Jan	chicken-Eggs
chicken	6	pol	Jan	chicken-Mutton-Eggs
chicken	7	sat	Jan	chicken
chicken	10	abc	Feb	chicken
Mutton	2	abc	Jan	Mutton
Mutton	4	sab	Jan	Mutton
Mutton	6	pol	Jan	chicken-Mutton-Eggs
Mutton	8	sat	Jan	Mutton
Eggs	3	xyz	Jan	chicken-Eggs
Eggs	5	frq	Jan	Eggs
Eggs	6	pol	Jan	chicken-Mutton-Eggs
Eggs	9	bat	Jan	Eggs
Eggs	11	abc	Feb	Eggs

I need customer wise combination.
Please check my required result in the question.

Thanks

[Anonymous]

@amitchandak 
I am still getting an error.