Lookup value corresponding to max date and concatenate

[dollarvora]

I've two tables as under:

ORDER:

ID	DATE	PURPOSE
1	01-02-2020	A
1	03-03-2020	B
1	03-03-2020	C
2	02-02-2020	A
2	01-02-2020	D
2	02-02-2020	E
3	04-05-2020	X
3	04-05-2020	Y
3	04-05-2020	Z
4	01-01-2020	M
5	06-07-2020	N

CUSTOMER:

ID

1

2

3

4

5

6

I'm wanting to match the ID's and extract the max date for each ID and also concatenate the PURPOSE's for those ID's

so the resulting table will be as under:

ID	MAX_DATE	PURPOSE
1	03-03-2020	B,C
2	02-02-2020	A,E
3	04-05-2020	X,Y,Z
4	01-01-2020	M
5	06-07-2020	N
6

Currently, to get the max date column I'm using the query below:

MAX_DATE =
VAR _MaxDate =
CALCULATE (
MAX ( ORDER[DATE] ),
FILTER ( ORDER,ORDER[ID] = CUSTOMER[ID] )
)
RETURN
LOOKUPVALUE (
ORDER[DATE],
ORDER[DATE], _MaxDate,
ORDER[ID], CUSTOMER[ID]

I'm not sure how do I get the PURPOSE column concatenated using the same or similar measure

[edhans]

You would use the CONCATENATEX() function. The basics are 

Measure =
CONCATENATEX(
    TableName,
    ColumnOrExpression,
    ", "
)

There is an excellent article here using CONCATENATEX() to debug measureshttps://dax.tips/2020/06/24/dax-1-column-fusion-pt-2/#:~:text=Iterator%20functions%20can%20be%20powe... . Phil was at our PowerPlatform usergroup last week and showed this trick. Very cool. 

CONCATENATEX will easily grab a bunch of text and put it together in any order you like with the delimiter you choose.

If you need more help, can you give us data in a good table format? The above just pastes into Excel or PowerBI as a long string. See links below.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum