Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Summarizing/grouping together columns

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 IDCustomer NameProduct PurchasedRegionModified Customer ID (Desired Column) 
124MicrosoftCloudUS124, 125, 126
124MicrosoftCloudUS124, 125, 126
125MicrosoftData CenterUS124, 125, 126
126MicrosoftData CenterUS124, 125, 126
223AmazonCloudUS223
224AmazonCloudEurope224

 

Screen Shot 2020-05-21 at 9.52.20 AM.png

3 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

If I understand correctly, perhaps:

 

 

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

camargos88
Community Champion
Community Champion

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], ",")
 
Capture.PNG


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

ChrisMendoza
Resident Rockstar
Resident Rockstar

@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 ()
)

image.png






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



View solution in original post

3 REPLIES 3
ChrisMendoza
Resident Rockstar
Resident Rockstar

@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 ()
)

image.png






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



camargos88
Community Champion
Community Champion

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], ",")
 
Capture.PNG


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Greg_Deckler
Super User
Super User

If I understand correctly, perhaps:

 

 

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.