Concatenated list from another table, with filter, and only distinct.

[DaveHepler]

I feel like I'm close but still losing here.

Sample data

I have a table like this, call it Project

Project	Name	Many other columns
1	A
2	B
3	C

And another like this, call it IDs

Project	ID
1	X
1	Y
2	X
3	Z
3	Z
3	Y

Desired Ouput, new column in Project table.

Project	Name	Desired new Column
1	A	X,Y
2	B	X
3	C	Y,Z

Current Dax

Site_IDS =

CONCATENATEX(DISTINCT(FILTER(IDs,IDs[PROJECT]=PROJECT[PROJECT])),IDs[ID],", ")

This works but instead of my desired output I get 

Project	Name	Desired new Column
1	A	X,Y
2	B	X
3	C	Y,Z,Z

The DISTINCT() doesnt appear to be doing anything at all.  The output is the same with or without it.   I feel like I'm missing something trivial.  I want only a list of distinct values from the IDs table matching the project number in the project table.   Any assistance would be greatly appreciated.

[tamerj1]

Hi @DaveHepler 

Establishe a relationship between the teo table using Project columns then create a measure

Site_IDS =

CONCATENATEX(VALUES(IDs[ID]),IDs[ID],", ")

[Anonymous · Accepted solution]

Hi  @DaveHepler ,

Here are the steps you can follow：

1. Create calculated table.

Table3 =
SUMMARIZE(
    'Table2',
    'Table2'[Project],'Table2'[ID])

2. Create calculated column.

Table3：

Column =
CONCATENATEX(
    FILTER(ALL(Table3),
    'Table3'[Project]=EARLIER('Table3'[Project])), [ID])

Table1：

Desired new Column =
CALCULATE(
    MAX('Table3'[Column]),
    FILTER(ALL('Table3'),
    'Table3'[Project]='Table1'[Project]))

3. Result:

If you need pbix, please click here.

Best Regards,

Liu Yang

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