Forum Discussion

apmulhearn's avatar
apmulhearn
Icon for Helper III rankHelper III
4 years ago
Solved

Lookup Multiple Values from Another Table & Concatenate with ; as delimeter

Hello,   I am working with two distinct tables which contain information about trips. I need Table2.AllEmailAddresses to contain all distinct matches from Table1.EmailAddress where the Status is Bo...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi apmulhearn ,

    You can create a calculated column as below in Table2 to get it:

    Column = 
CONCATENATEX (
    FILTER (
        'Table1',
        'Table1'[DepartureID] = 'Table2'[Departurel]
            && 'Table1'[Status] = "Booked"
    ),
    'Table1'[EmailAddress],
    ";",
    'Table1'[EmailAddress],
    ASC
)

    By the way, why the email address ā€œ[email protected]ā€ not be listed under the Departurel as "LGA:356985895"?

    Best Regards

parry2k's avatar
parry2k
Icon for Super User rankSuper User
4 years ago

apmulhearn assuming Table2 has a relationship with Table1 on DepartureID column which will one to many relationship, one will be on Table2 side and many will be on Table1 side, add a new column in Table2 with the following expression:

 

Email Address = 
VAR __table = CALCULATETABLE ( RELATEDTABLE ( Table1 ), Table1[Status] = "Booked" )
RETURN

CONCATENATEX ( SUMMARIZE ( __table, [DepartureId], [Email] ),  [Email], ";" )

 

āœØ Follow us on LinkedIn and  to our YouTube channel

I would ā¤ Kudos if my solution helped. šŸ‘‰ If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!