Look up value and combine into one cell

[jcastr02]

Hello I have two tables.  I am trying to get information into Table 2 - Line of  Business Look up.  As you see EID in TAble 1 has EID 1493615 has two values (Pharmacy and Data Entry) , I'd like both of those values to go into a cell as shown in Table 2 under hte Line of business Lookup column.   SEeing if there is a calculated column that could be added for it to show like below in table 2.

TABLE 1			TABLE 2
EID	Line of Business		EID	Line of Business Lookup
1493615	Pharmacy		1493615	Pharmacy; Data Entry
4171065	Pharmacy		4171065	Pharmacy
4203520	Pharmacy		4203520	Pharmacy
1498994	Pharmacy		1498994	Pharmacy
2262366	Pharmacy		2262366	Pharmacy
2665329	Pharmacy
4031705	Pharmacy
4032981	Pharmacy
4050301	Pharmacy
4051236	Pharmacy
1493615	Data Entry

[PaulDBrown]

Try:

Line of Business =
COCATENATEX(

   TREATAS(VALUES(Table 2 [EID]), Table 1 [EID]),

Table 1 [EID], "; ")

[jcastr02]

Thanks @PaulDBrown   It seems I am doing something wrong?

[PaulDBrown]

You need to close the TREATAS brackets after the first  'Cross trained' [EID]

[jcastr02]

@PaulDBrown Thank you.  Right now it's pulling over EIDs  I'm trying to get it to pull Line of Business when the EID matches (like in second column of table 2)

[PaulDBrown]

Apologies. My mistake. Try:

Line of Business =
COCATENATEX(

   TREATAS(VALUES(Table 2 [EID]), Table 1 [EID]),

Table 1 [Line of business], "; ")

[jcastr02]

@PaulDBrown  Thanks ...it looks like when I try to type from table 1, the only option that comes up is the EID, it won't let me pull Line of Business over

[PaulDBrown · Accepted solution]

Apologies again. This is what happens when you are trying to solve more complex DAX from your phone so I must stop doing it!!
Ok, I have now been able to test this:
For a calculated column:

Line of Business =
VAR _Table =
    CALCULATETABLE (
        VALUES ( 'Table 1'[Line of Business] ),
        FILTER ( 'Table 1', 'Table 2'[EID] =  'Table 1'[EID]  )
    )
RETURN
    CONCATENATEX ( _Table, 'Table 1'[Line of Business], "; " )

As a measure:

Line of Business =
VAR _Table =
    CALCULATETABLE (
        VALUES ( 'Table 1'[Line of Business] ),
        'Table 2'[EID] IN VALUES ( 'Table 1'[EID] )
    )
RETURN
    CONCATENATEX ( _Table, 'Table 1'[Line of Business], "; " )

[jcastr02]

@PaulDBrown Yes!!! that worked, you are awesome thank you!