Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to Solution.
You are right. Please try
NewColumn =
CONCATENATEX (
CALCULATETABLE ( VALUES ( transaction[Text] ) ),
transaction[Text],
UNICHAR ( 10 ),
transaction[Text], ASC
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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
)
@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?
You are right. Please try
NewColumn =
CONCATENATEX (
CALCULATETABLE ( VALUES ( transaction[Text] ) ),
transaction[Text],
UNICHAR ( 10 ),
transaction[Text], ASC
)
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
13 |
User | Count |
---|---|
91 | |
86 | |
46 | |
28 | |
21 |