Calculated column with a entries for each order from different table

[joshua1990]

Hi all!

I have a Sales Master table that contains for each order 1 row:

Order Nr	Value
1	5
2	6

Then I have a transaction table with multiple rows for each sales order:

Order Nr	Text
1	ABC
1	ABC
1	ADF
1	KIG
2	B

Now I would like to add a calculated column to the sales master that contains all distinct text entries for each order from the second table, seperated with a commata.

How is this possible?

[CNENFRNL]

[tamerj1]

Hi @joshua1990 

assume that you have a one to many relationship between the two tables based on [Prder Nr] columns then

NewColumn =
CONCATENATEX (
    RELATEDTABLE ( transaction ),
    transaction[Text],
    UNICHAR ( 10 ),
    transaction[Text], ASC
)

[joshua1990]

@tamerj1 : Thanks a lot! Yes, there is a 1:n relation between both tables.

It works, but not in the way I would like to see. I get as a string all values now, but I would like to get just distinct / unique values. How can I exclude duplictes here?

[tamerj1 · Accepted solution]

@joshua1990 

You are right. Please try

NewColumn =
CONCATENATEX (
    CALCULATETABLE ( VALUES ( transaction[Text] ) ),
    transaction[Text],
    UNICHAR ( 10 ),
    transaction[Text], ASC
)