Forum Discussion
Responsive ResidentRetrieve text from column (Sort of Distinctcount for text)
HI all,
Tried to find a solution but couldn't find one.
I have a column with Client Name and a column called "Service Name" where all services contracted are included.
In the example below, Client A has 3 different "Service Name" ---> Service 1, Service 2 and Service 3
| Client_Name | RollCall_Date | SegmentName | ServiceName |
| Client A | 01-mar-22 | Majors | Service 1 |
| Client A | 01-mar-22 | Majors | Service 1 |
| Client A | 01-mar-22 | Majors | Service 2 |
| Client A | 01-mar-22 | Majors | Service 2 |
| Client A | 01-mar-22 | Majors | Service 2 |
| Client A | 01-mar-22 | Majors | Service 2 |
| Client A | 01-mar-22 | Majors | Service 1 |
| Client A | 01-mar-22 | Majors | Service 1 |
| Client A | 01-mar-22 | Majors | Service 1 |
| Client A | 01-mar-22 | Majors | Service 1 |
| Client A | 01-mar-22 | Majors | Service 3 |
| Client A | 01-mar-22 | Majors | Service 3 |
| Client A | 01-mar-22 | Majors | Service 3 |
| Client A | 01-mar-22 | Majors | Service 3 |
Currenly and using "distinc Count" I can show how many Services are included x customer.
What I would like to do is create a new column where the names of the different services appear in text, not just the number. But I am not sure if this is possible.
IN this case, next to the #ServiceName column showing the different services contracted, another one showing which services.
Service 1 & Service 2
Service 1 & Service 3
Service 1, service 2 & Service 3, etc.
Thanks
I was able to find a solution from @v-juanli-msft that should work for you.
In your case the calculated column would be written as
Distinct Services =CONCATENATEX(FILTER(SUMMARIZE(clientTable,clientTable[Client_Name],clientTable[ServiceName]),clientTable[Client_Name]=EARLIER(clientTable[Client_Name])),clientTable[ServiceName]," & ")Hope this helps.
1 Reply
jgeddesSuper User
I was able to find a solution from @v-juanli-msft that should work for you.
In your case the calculated column would be written as
Distinct Services =CONCATENATEX(FILTER(SUMMARIZE(clientTable,clientTable[Client_Name],clientTable[ServiceName]),clientTable[Client_Name]=EARLIER(clientTable[Client_Name])),clientTable[ServiceName]," & ")Hope this helps.