Summarizing/grouping together columns

[Anonymous]

Hi all, have a table of customers, some of which have multiple Customer IDs (like Microsoft in the sample table), but I really would like to classify this as one customer. I'd like to group customers by Customer Name only if the Regions are the same by creating the Modified Customer ID column, which should include a list of unique Customer IDs separated by a comma.

So, in this case, Microsoft would be a customer I'd want to group into 1 customer because it has the same region for all Customer IDs, but I would not want to group Amazon as 1 customer because one is in Europe and one is in the US. Please comment if you have any ideas!

Customer ID	Customer Name	Product Purchased	Region	Modified Customer ID (Desired Column)
124	Microsoft	Cloud	US	124, 125, 126
124	Microsoft	Cloud	US	124, 125, 126
125	Microsoft	Data Center	US	124, 125, 126
126	Microsoft	Data Center	US	124, 125, 126
223	Amazon	Cloud	US	223
224	Amazon	Cloud	Europe	224

[Greg_Deckler · Accepted solution]

If I understand correctly, perhaps:

Column =
  CONCATENATEX(
    FILTER('Table',[Customer Name] = EARLIER([Customer Name]) && [Region] = EARLIER([Region]))
    [Customer ID],", "
  )

[camargos88 · Accepted solution]

Hi @Anonymous ,

Try this one:

Column =

VAR _customer = CALCULATETABLE(DISTINCT('Table'[Customer ID]), FILTER('Table', 'Table'[Region] = EARLIER('Table'[Region]) && 'Table'[Customer Name] = EARLIER('Table'[Customer Name])))

RETURN CONCATENATEX(_customer, 'Table'[Customer ID], ",")

[ChrisMendoza · Accepted solution]

@Anonymous -

Measure solution attempt:

Measure Modified Customer ID = 
IF (
    ISFILTERED ( 'TableName'[Customer Name] ),
    CALCULATE (
        CONCATENATEX (
            DISTINCT ( TableName[Customer ID] ),
            TableName[Customer ID],
            ", "
        ),
        ALLEXCEPT ( 'TableName', 'TableName'[Customer Name], 'TableName'[Region] )
    ),
    BLANK ()
)

[camargos88 · Accepted solution]

Hi @Anonymous ,

Try this one:

Column =

VAR _customer = CALCULATETABLE(DISTINCT('Table'[Customer ID]), FILTER('Table', 'Table'[Region] = EARLIER('Table'[Region]) && 'Table'[Customer Name] = EARLIER('Table'[Customer Name])))

RETURN CONCATENATEX(_customer, 'Table'[Customer ID], ",")

[Greg_Deckler · Accepted solution]

If I understand correctly, perhaps:

Column =
  CONCATENATEX(
    FILTER('Table',[Customer Name] = EARLIER([Customer Name]) && [Region] = EARLIER([Region]))
    [Customer ID],", "
  )