topic Re: Calculated column with a entries for each order from different table in DAX Commands and Tips

Calculated column with a entries for each order from different table

joshua1990 — Mon, 29 Aug 2022 12:24:32 GMT

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?

Re: Calculated column with a entries for each order from different table

tamerj1 — Mon, 29 Aug 2022 12:30:09 GMT

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 )

Re: Calculated column with a entries for each order from different table

joshua1990 — Mon, 29 Aug 2022 19:29:45 GMT

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?

Re: Calculated column with a entries for each order from different table

CNENFRNL — Tue, 30 Aug 2022 00:00:52 GMT

Re: Calculated column with a entries for each order from different table

tamerj1 — Tue, 30 Aug 2022 02:58:40 GMT

joshua1990

You are right. Please try

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