Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Concate Customer Wise

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
chicken1abcJanchicken
chicken3xyzJanchicken,Eggs
chicken6polJanchicken,Mutton,Eggs
chicken7satJanchicken
chicken10abcFebchicken
Mutton2abcJanMutton
Mutton4sabJanMutton
Mutton6polJanchicken,Mutton,Eggs
Mutton8satJanMutton
Eggs3xyzJanchicken,Eggs
Eggs5frqJanEggs
Eggs6polJanchicken,Mutton,Eggs
Eggs9batJanEggs
Eggs11abcFebEggs

 

 

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

Item BillIdCustomerIdMonthCombine
chicken1abcJanchicken,Mutton
chicken3xyzJanchicken,Eggs
chicken6polJanchicken,Mutton,Eggs
chicken7satJanchicken,Mutton
chicken10abcFebchicken,Eggs
Mutton2abcJanchicken,Mutton
Mutton4sabJanMutton
Mutton6polJanchicken,Mutton,Eggs
Mutton8satJanchicken,Mutton
Eggs3xyzJanchicken,Eggs
Eggs5frqJanEggs
Eggs6polJanchicken,Mutton,Eggs
Eggs9batJanEggs
Eggs11abcFebchicken,Eggs
Please help me in correction of this Dax
thanks
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

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

       

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        4 years ago

        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's avatar
    Anonymous
    Not applicable
    4 years ago

    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