Concatenate the distinct values in a single column

[Anonymous]

Hello All, 

i have a table 

Circuit_Name	CONTRACT_ACCOUNT_NUMBER	CommunityName
ARDMORE 24	322382	Ardmore
ARDMORE 24	322385	Ardmore
ARDMORE 24	322391	Ardmore
ARDMORE 24	322395	Ardmore
ARDMORE 24	322400	Ardmore
ARDMORE 24	131599652	Ardmore
ARDMORE 24	131600759	Ardmore
ARDMORE 24	131601250	Ardmore
ARDMORE 24	131601585	Ardmore
ARDMORE 24	2000764	Marietta
ARDMORE 24	127716387	Marietta
ARDMORE 24	128010672	Marietta
ARDMORE 24	128226326	Marietta
ARDMORE 24	128355644	Marietta
ARDMORE 24	128566177	Marietta
ARDMORE 24	128842562	Marietta
ARDMORE 24	128958608	Marietta
ARDMORE 24	129765752	Marietta
ARDMORE 24	131003296	Marietta
ARDMORE 24	131113786	Marietta
ARDMORE 24	131136092	Marietta
ARDMORE 24	131145499	Marietta
ARDMORE 24	131491299	Marietta
ARDMORE 24	129488236	Stratford
ARDMORE 24	130928636	Sulphur

I tried using both the Table Visual and Matrix Visual

But unable to show everything in one column like below 

now i need to show all the communities in a one column 

Circuit_Name	CommunityName
ARDMORE 24	Ardmore,Marietta,Stratford,Sulphur

[mahoneypat]

Please try this expression

List of Names = CONCATENATEX(DISTINCT(Table[Community Name]), Table[Community Name], ", ")

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

the only issue with the above query is
the list of communities is not the exactly same as ARDMORE
just for ARDMORE we have 4 different communities
but for other Circuit there are many
if i use the above query i am getting something like this

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Try this:

Column =
CONCATENATEX (
    FILTER ( 'Table', 'Table'[Circuit_Name] = EARLIER ( 'Table'[Circuit_Name] ) ),
    'Table'[CommunityName],
    ","
)

If you only want to target Ardmore 24, please try add if statement.

Column =
IF('Table'[Circuit_Name] ="ARDMORE 24",
CONCATENATEX (
    FILTER ( 'Table', 'Table'[Circuit_Name] = EARLIER ( 'Table'[Circuit_Name] ) ),
    'Table'[CommunityName],
    ","
),'Table'[CommunityName]
)

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

You could modify with this approach to show the names only when there is a reasonable number.

List of Names = var names = DISTINCT(Table[Community Name])

return IF(COUNTROWS(names)<=5, CONCATENATEX(names, Table[Community Name], ", "), "More than 5 names")

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat