Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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 |
Solved! Go to Solution.
If I understand correctly, perhaps:
Column =
CONCATENATEX(
FILTER('Table',[Customer Name] = EARLIER([Customer Name]) && [Region] = EARLIER([Region]))
[Customer ID],", "
)
Hi @Anonymous ,
Try this one:
@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 ()
)
Proud to be a Super User!
@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 ()
)
Proud to be a Super User!
Hi @Anonymous ,
Try this one:
If I understand correctly, perhaps:
Column =
CONCATENATEX(
FILTER('Table',[Customer Name] = EARLIER([Customer Name]) && [Region] = EARLIER([Region]))
[Customer ID],", "
)
User | Count |
---|---|
85 | |
81 | |
64 | |
53 | |
46 |
User | Count |
---|---|
101 | |
49 | |
42 | |
39 | |
38 |