cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Concatenate the distinct values in a single column

Hello All, 

i have a table 

Circuit_NameCONTRACT_ACCOUNT_NUMBERCommunityName
ARDMORE 24322382Ardmore
ARDMORE 24322385Ardmore
ARDMORE 24322391Ardmore
ARDMORE 24322395Ardmore
ARDMORE 24322400Ardmore
ARDMORE 24131599652Ardmore
ARDMORE 24131600759Ardmore
ARDMORE 24131601250Ardmore
ARDMORE 24131601585Ardmore
ARDMORE 242000764Marietta
ARDMORE 24127716387Marietta
ARDMORE 24128010672Marietta
ARDMORE 24128226326Marietta
ARDMORE 24128355644Marietta
ARDMORE 24128566177Marietta
ARDMORE 24128842562Marietta
ARDMORE 24128958608Marietta
ARDMORE 24129765752Marietta
ARDMORE 24131003296Marietta
ARDMORE 24131113786Marietta
ARDMORE 24131136092Marietta
ARDMORE 24131145499Marietta
ARDMORE 24131491299Marietta
ARDMORE 24129488236Stratford
ARDMORE 24130928636

Sulphur

 

I tried using both the Table Visual and Matrix Visual

Test_Image.PNG

But unable to show everything in one column like below 

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

Circuit_NameCommunityName
ARDMORE 24Ardmore,Marietta,Stratford,Sulphur
1 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.

View solution in original post

4 REPLIES 4
mahoneypat
Super User IV
Super User IV

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

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

rasala583_0-1596667839521.png

 

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.

View solution in original post

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors