Forum Discussion
Helper IIILookup 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 Booked. It should ignore any other Status values. If an email address is used multiple times for a DepartureID, that email address should only be included once. Sample Image below with "AllEmailAddress" in Table2 completed with ideal result.
Many thanks for any help.
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
4 Replies
- parry2k
Super User
apmulhearn could you please clarify further what you are looking for? Not sure what the ask is.
apmulhearnSure, I can try again.
Basically, I need to be able to lookup values in table 1 and have them populate a column in table 2. However, there will often be more than 1 match on table 1. I need all matching values from table 1 to populate in the column in table 2, separated by a semicolon.
- parry2k
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!
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
