Retrieve text from column (Sort of Distinctcount for text)

[romovaro]

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

@jgeddes 

[jgeddes · Accepted solution]

I was able to find a solution from @v-juanli-msft that should work for you.

 https://community.powerbi.com/t5/Desktop/Concatenate-multiple-row-values-into-one-based-on-unique-ID... 

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.